package com.hongdee.crm.task.service.impl;

import com.hongdee.common.util.DateUtils;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.crm.Constant;
import com.hongdee.crm.carConfig.dao.ITCrmCarRefDao;
import com.hongdee.crm.carConfig.service.ITCrmCarRefService;
import com.hongdee.crm.task.dao.IBcMaintainTypeTaskDao;
import com.hongdee.crm.task.dao.ITCrmCarMaintainTaskDao;
import com.hongdee.crm.task.dao.ITCrmTaskDao;
import com.hongdee.crm.task.dao.ITCrmTaskUserCountDao;
import com.hongdee.crm.task.entity.TCrmTask;
import com.hongdee.crm.task.entity.TCrmTaskAssignLog;
import com.hongdee.crm.task.entity.TCrmTaskReal;
import com.hongdee.crm.task.entity.TCrmTaskUserCount;
import com.hongdee.crm.task.mapper.ITCrmTaskRealMapper;
import com.hongdee.crm.task.service.ITCrmCarMaintainTaskService;
import com.hongdee.crm.task.service.ITCrmTaskService;
import com.hongdee.crm.task.service.ITCrmTimeTaskService;
import com.hongdee.crm.task.vw.service.ITCrmVwTaskService;
import com.hongdee.crm.taskConfig.service.ITCrmCarTaskConfService;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;


/**
 * 名  称:
 * 类  型: class
 * 描  述: 处理任务相关service
 * 路  径:com.hongdee.crm.task.service.impl
 * 作  者:liuze
 * 日  期:2017/2/28 10:25
 */
@Service
public class TCrmTaskServiceImpl implements ITCrmTaskService {

    @Autowired
    private ITCrmTaskDao taskDao;
    @Autowired
    private ITCrmCarRefDao carRefDao;
    @Autowired
    private IBcMaintainTypeTaskDao maintainTypeTaskDao;
    @Autowired
    private ITCrmCarRefDao crmCarRefDao;
    @Autowired
    private ITCrmTaskUserCountDao userCountDao;

    @Autowired
    private ITCrmCarMaintainTaskDao crmCarMaintainTaskDao;

    @Autowired
    private ITCrmTaskRealMapper itCrmTaskRealMapper;

    @Resource(name = "firstCarMaintainTaskService")
    private ITCrmCarMaintainTaskService crmCarMaintainTaskService;
    @Resource(name = "TwoCarMaintainTaskService")
    private ITCrmCarMaintainTaskService twoCrmCarMaintainTaskService;
    @Resource(name = "dbCarMaintainTaskService")
    private ITCrmCarMaintainTaskService dbCarMaintainTaskService;
    @Resource(name = "FirstMonthMaintainTaskService")
    private ITCrmCarMaintainTaskService firstMonthMaintainTaskService;
    @Autowired
    private ITCrmVwTaskService vwTaskService;
    @Autowired
    private ITCrmTimeTaskService timeTaskService;
    @Autowired
    private ITCrmCarTaskConfService taskConfService;
    //卡券生成任务
    @Autowired
    private ITCrmTimeTaskService crmTimeTaskService;

    @Autowired
    private ITCrmCarRefService crmCarRefService;

    private Logger logger = LoggerFactory.getLogger(TCrmTaskServiceImpl.class);

    private final int LIMIT = 1000;
    private final String ROOTBXCODE = "bx";

    /**
     * 关爱 生日提醒
     *
     * @param companyId
     * @throws Exception
     */
    @Override
    public Integer guanAiShengRi(String companyId, String startTime, String endTime) throws Exception {
        String code = "sr";
        String pCode = "ga";
        String tpl = "[%s]的车主[%s]将于%s过生日";
        int start = 0;
        Integer nums = 0;
        Integer beferDay = taskDao.beforDays(companyId, code, pCode + "_" + code);
        if (beferDay != null) {
            while (true) {
                List<Map<String, Object>> cars = this.taskDao.guanaishengri(companyId, beferDay, start * LIMIT, LIMIT, pCode, code);
                if (cars.size() == 0) {
                    start = 0;
                    break;
                }
                List<TCrmTask> taskList = new ArrayList<>();
                for (Map<String, Object> car : cars) {
                    String plateNum = null;
                    if (car.get("plate_num") == null) {
                        String vin = String.valueOf(car.get("vin"));
                        plateNum = vin.substring(vin.length() - 6, vin.length());
                        car.put("plate_num", plateNum);
                    } else {
                        plateNum = String.valueOf(car.get("plate_num"));
                    }
                    String content = String.format(tpl, plateNum, car.get("owner_name"), car.get("bday").toString());
                    String expectDate = DateUtils.getTimeStr("YYYY") + "-" + car.get("expectdate").toString();
                    boolean flag = taskDao.checkTaskByVinAndId(companyId, StringUtil.formatEmpty(car.get("vin")), StringUtil.formatEmpty(car.get("id")), code, pCode, expectDate);
                    if (!flag) {
                        //判断日期范围
                        boolean timeFlag = true;
                        if (!"".equals(startTime) && !"".equals(endTime)) {
                            timeFlag = DateUtils.checkTime(expectDate, startTime, endTime);
                        }
                        if (timeFlag) {
                            TCrmTask task = this.createCrmTask(companyId, car, expectDate, content, pCode, code);
                            taskList.add(task);
                        }
                    }
                }
                nums += taskList.size();
                crmCarMaintainTaskDao.insertMaintailLists(taskList);
                logger.info("关爱生日任务第" + start + "结束" + System.currentTimeMillis());
                start++;
            }
        }
        return nums;
    }

    /**
     * 生成 任务实体类
     *
     * @param companyId
     * @param car
     * @param expectDate
     * @param content
     * @param pcode
     * @param code
     * @return
     */
    private TCrmTask createCrmTask(String companyId, Map<String, Object> car, String expectDate, String content, String pcode, String code) {
        TCrmTask task = new TCrmTask();
        task.setCreateTime(new Date().getTime());
        task.setCompanyId(companyId);
        task.setVIN(StringUtil.formatEmpty(car.get("vin")));
        task.setCarId(StringUtil.formatEmpty(car.get("id")));
        task.setPlateNum(String.valueOf(car.get("plate_num")));
        task.setLinkman(String.valueOf(car.get("owner_name")));
        if (car.get("contact_tel") != null) {
            task.setLinkphone(car.get("contact_tel").toString());
        }
        if (expectDate != null) {
            task.setExpectDate(expectDate);
        } else {
            task.setExpectDate(car.get("txrq").toString());
        }
        task.setStatus("0");
        task.setIsBack("0");
        task.setTypePCode(pcode);
        task.setTypeCode(code);
        task.setContent(content);
        return task;
    }

    /**
     * 关爱 质保任务
     *
     * @param companyId
     * @throws Exception
     */
    @Override
    public Integer guanAiZhiBao(String companyId, String startTime, String endTime) throws Exception {
        String code = "zbt";
        String pCode = "ga";
        String tpl = "%s的车，车主[%s],%s质保日，[质保时间]%s";
        Integer nums = 0;
        Integer beferDay = this.taskDao.beforDays(companyId, code, pCode + "_" + code);
        if (beferDay != null) {
            int start = 0;
            while (true) {
                List<Map<String, Object>> cars = this.taskDao.guanAiZhiBao(companyId, beferDay.intValue(), start * LIMIT, LIMIT, pCode, code);
                if (cars.size() == 0) {
                    start = 0;
                    break;
                }
                List<TCrmTask> taskList = new ArrayList<>();
                for (Map<String, Object> car : cars) {
                    String plateNum = null;
                    if (car.get("plate_num") == null) {
                        String vin = String.valueOf(car.get("vin"));
                        plateNum = vin.substring(vin.length() - 6, vin.length());
                        car.put("plate_num", plateNum);
                    } else {
                        plateNum = String.valueOf(car.get("plate_num"));
                    }
                    String content = String.format(tpl, plateNum, car.get("owner_name").toString(),
                            car.get("zbr").toString(), car.get("zbsj").toString());
                    boolean flag = taskDao.checkSbTask(companyId, car.get("id").toString(), code, pCode, car.get("txrq").toString());
                    if (!flag) {
                        //判断日期范围
                        boolean timeFlag = true;
                        if (!"".equals(startTime) && !"".equals(endTime)) {
                            timeFlag = DateUtils.checkTime(car.get("txrq").toString(), startTime, endTime);
                        }
                        if (timeFlag) {
                            TCrmTask task = this.createCrmTask(companyId, car, null, content, pCode, code);
                            taskList.add(task);
                        }
                    }
                }
                nums += taskList.size();
                crmCarMaintainTaskDao.insertMaintailLists(taskList);
                start++;
            }
        }
        return nums;
    }

    /**
     * 关爱 延保任务
     *
     * @param companyId
     * @throws Exception
     */
    @Override
    public Integer guanAiYanBao(String companyId, String startTime, String endTime) throws Exception {
        String code = "ybt";
        String pCode = "ga";
        String tpl = "%s的车主[%s]将于%s延保到期,当前行驶里程[%s]km";
        Integer nums = 0;
        Integer beferDay = this.taskDao.beforDays(companyId, code, pCode + "_" + code);
        if (beferDay != null) {
            int start = 0;
            while (true) {
                List<Map<String, Object>> cars = this.taskDao.guanAiYanBao(companyId, beferDay.intValue(), start * LIMIT, LIMIT, pCode, code);
                if (cars.size() == 0) {
                    start = 0;
                    break;
                }
                List<TCrmTask> taskList = new ArrayList<>();
                for (Map<String, Object> car : cars) {
                    String plateNum = null;
                    if (car.get("plate_num") == null) {
                        String vin = String.valueOf(car.get("vin"));
                        plateNum = vin.substring(vin.length() - 6, vin.length());
                        car.put("plate_num", plateNum);
                    } else {
                        plateNum = String.valueOf(car.get("plate_num"));
                    }
                    String content = String.format(tpl, plateNum, car.get("owner_name"), car.get("ybdq").toString(), String.valueOf(car.get("run_mile")));
                    boolean flag = taskDao.checkTaskByVinAndId(companyId, StringUtil.formatEmpty(car.get("vin")), StringUtil.formatEmpty(car.get("id")), code, pCode, car.get("txrq").toString());
                    if (!flag) {
                        boolean timeFlag = true;
                        if (!"".equals(startTime) && !"".equals(endTime)) {
                            timeFlag = DateUtils.checkTime(car.get("txrq").toString(), startTime, endTime);
                        }
                        if (timeFlag) {
                            TCrmTask task = this.createCrmTask(companyId, car, null, content, pCode, code);
                            taskList.add(task);
                        }
                    }

                }
                nums += taskList.size();
                crmCarMaintainTaskDao.insertMaintailLists(taskList);
                start++;
            }
        }
        return nums;
    }

    /**
     * 关爱 流失任务
     *
     * @param companyId
     * @throws Exception
     */
    @Override
    public Integer liuShi(String companyId, String startTime, String endTime) throws Exception {
        String pCode = "ls";
        String tpl = "%s 的车，车主[%s]将于[%s]起，超过%d个月，处于准流失状态";
        Integer nums = 0;
        //查询有设置
        List<Map<String, Object>> mapList = maintainTypeTaskDao.findCompanyByPCodeAndCompanyId(pCode, companyId);
        if (mapList != null) {
            //遍历状态信息 也就是月份
            for (Map<String, Object> map : mapList) {
                String month = String.valueOf(map.get("code"));
                //获得其月份对应的天数
                Integer beferDay = this.taskDao.beforDays(companyId, month, pCode + "_" + month);
//                if (beferDay == null) {
//                    continue;
//                }
                logger.info("流失任务 " + month + "月流失开始 " + System.currentTimeMillis());
                int start = 0;
                while (true) {
                    //鲁A9Q165的车，车主[李淑霞（润通）]将于2016-11-23起，超过7个月，处于准流失状态
                    List<Map<String, Object>> cars = null;
                    if (!"".equals(startTime) && !"".equals(endTime)) {
                        cars = this.taskDao.liuShi(companyId, Integer.valueOf(month), beferDay, start * LIMIT, LIMIT, pCode);
                    } else {
                        cars = this.taskDao.liuShiIsEmpty(companyId, Integer.valueOf(month), beferDay, start * LIMIT, LIMIT, pCode);
                    }
                    if (cars.size() == 0) {
                        start = 0;
                        break;
                    }
                    List<TCrmTask> taskList = new ArrayList<>();
                    for (Map<String, Object> car : cars) {
                        boolean flag = taskDao.checkLsTaskByVin(companyId, String.valueOf(car.get("vin")), month, pCode);
                        if (!flag) {

                            String plateNum = null;
                            if (car.get("plate_num") == null) {
                                String vin = String.valueOf(car.get("vin"));
                                plateNum = vin.substring(vin.length() - 6, vin.length());
                                car.put("plate_num", plateNum);
                            } else {
                                plateNum = String.valueOf(car.get("plate_num"));
                            }
                            String liuShiTxrq = this.taskDao.liuShiTxrq(StringUtil.formatNull(car.get("vin")), Integer.valueOf(month), companyId);
                            if (liuShiTxrq == null) {
                                continue;
                            }
                            //根据流失提醒日期-去提前天数
                            Long liushiEx = DateUtils.addDayLongTime(DateUtils.dayToLongTime(liuShiTxrq), -beferDay);
                            //验证六个月流失是否在一个月内有保养任务，如果有保养任务，则不生在生成流失任务
                            flag = taskDao.checkLsByBYTask(companyId, car.get("id").toString(), month, pCode, liushiEx);
                            if (flag) {
                                continue;
                            }
                            String content = String.format(tpl, plateNum, car.get("owner_name"), liuShiTxrq, Integer.valueOf(month));
                            boolean timeFlag = false;
                            Long liushiTxrqL = DateUtils.dayToLongTime(liuShiTxrq);
                            int m = Integer.valueOf(DateUtils.getTimeStr(new Date(liushiTxrqL), "yyyyMM"));
                            int nowM = Integer.valueOf(DateUtils.getTimeStr(new Date(), "yyyyMM"));
                            if (m == nowM) {
                                timeFlag = true;
                            } else {
                                timeFlag = false;
                            }
                            if (!"".equals(startTime) && !"".equals(endTime)) {
                                timeFlag = DateUtils.checkTime(DateUtils.formatLongTime(liushiEx), startTime, endTime);
                            }
                            //判断提醒日期是否为当月

                            if (timeFlag) {
                                String expectDate = DateUtils.formatLongTime(liushiEx, "yyyy-MM-dd");
                                TCrmTask task = this.createCrmTask(companyId, car, expectDate, content, pCode, month);
                                taskList.add(task);
                            }
                        }
                    }
                    nums += taskList.size();
                    crmCarMaintainTaskDao.insertMaintailLists(taskList);
                    start++;
                }
                logger.info("流失任务 " + month + "月流失结束 " + System.currentTimeMillis());
            }
        }
        return nums;
    }

    public Integer nianShenTest(String companyId, String startTime, String endTime) throws Exception {
        String code = "ns";
        String pcode = "ga";
        Integer nums = 0;
//        CrmTask.removeCrmTask(companyId,startTime,endTime,code,pcode);
        int start = 0;
        int limit = 5000;

//        while(true){
//            int num=0;
//            List<TCrmTask> taskList=new ArrayList<>();
//            //获得提前天数
//            Integer beferDay=taskDao.beforDays(companyId,code,pcode+"_"+code);
//            if(beferDay==null){
//                break;
//            }
//            List<Map<String,Object>> res = this.taskDao.nianShenPageForCars(startTime,endTime,companyId,start*limit,limit);
//            if(res.size()==0){
//                start=0;
//                break;
//            }
//            for (Map<String,Object> mTmp:res) {
//                TCrmTask task = new TCrmTask();
//                task.setCompanyId(companyId);
//                task.setVIN(StringUtil.formatNull(mTmp.get("vin")));
//                task.setCarId(StringUtil.formatNull(mTmp.get("carid")));
//                task.setPlateNum(StringUtil.formatNull(mTmp.get("plateNum")));
//                task.setLinkman(StringUtil.formatNull(mTmp.get("ownerName")));
//                task.setLinkphone(StringUtil.formatNull(mTmp.get("contactTel")));
//                task.setExpectDate(StringUtil.formatNull(mTmp.get("execpectDate")));
//                task.setStatus("0");
//                task.setTypePCode(code);
//                task.setTypeCode(pcode);
//                task.setContent(String.format("%s的车,车主[%s]，将于%s进行年审",task.getPlateNum(),task.getLinkman(),StringUtil.formatNull(mTmp.get("nianShenDate"))));
//                boolean flag=CrmTask.checkCrmTask(startTime,endTime,task);
//                if(!flag){
//                    taskList.add(task);
//                }
//            }
//            if(taskList.size()>0) {
//                num+=taskList.size();
//                crmCarMaintainTaskDao.insertMaintailLists(taskList);
//            }
//            nums+=num;
//            start++;
//        }
        return nums;
    }

    @Override
    public Integer nianShen(String companyId, String startTime, String endTime) throws Exception {
        //鲁H103VM的车， 车主[张可领（不祥）]，将于12-18进行年审
        List<Map<String, Object>> res = null;
        int pcount = 5000;
        int i = 0;
        String sTmp;
        long lTmp = 0;
        Date dTmp = null;
        Integer nums = 0;
        String code = "ns";
        String pcode = "ga";
        //当前年
        int nYear = Integer.valueOf(DateUtils.getTimeStr("yyyy"));
        //当前月
        int nMonth = Integer.valueOf(DateUtils.getTimeStr("MM"));
        int cYear = 0;

        while (true) {
            Integer beferDay = taskDao.beforDays(companyId, code, pcode + "_" + code);
            if (beferDay == null) {
                break;
            }
            res = this.taskDao.pageForCars(companyId, i * pcount, pcount, pcode, code);
            if (res == null)
                break;
            //处理业务
            for (Map<String, Object> mTmp : res) {
                //获取 guarantee_start_date 质保开始时间
                if (mTmp.get("guaranteeStartDate") == null)
                    continue;
                lTmp = (long) mTmp.get("guaranteeStartDate");
                dTmp = new Date(lTmp);
                //获取质保开始时间年份
                cYear = Integer.valueOf(DateUtils.getTimeStr(dTmp, "yyyy"));
                if ((nYear - cYear) <= 6) {//小于6年，2年一次
                    if ((nYear - cYear) % 2 == 0) { //先判断是否本年提醒
                        //处理当年具体提醒时间
                        String tmp = String.valueOf(nYear) + "-" + DateUtils.getTimeStr(dTmp, "MM-dd");
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        Calendar c = Calendar.getInstance();
                        c.setTime(sdf.parse(tmp));
                        c.add(Calendar.DATE, -beferDay);
                        this.checkSaveNianShen(startTime, endTime, companyId, mTmp, dTmp, c, nMonth);
                    }
                } else if ((nYear - cYear) <= 15) {// 6-15年，1年一次
                    //处理当年具体提醒时间
                    String tmp = String.valueOf(nYear) + "-" + DateUtils.getTimeStr(dTmp, "MM-dd");
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Calendar c = Calendar.getInstance();
                    c.setTime(sdf.parse(tmp));
                    c.add(Calendar.DATE, -beferDay);
                    this.checkSaveNianShen(startTime, endTime, companyId, mTmp, dTmp, c, nMonth);
                } else {//大于15年的处理，半年一次
                    //先取年审月
                    int m = Integer.valueOf(DateUtils.getTimeStr(dTmp, "MM"));
                    //计算下一年审月
                    if (m < 6) {
                        m = m + 6;
                    } else if (m == 6) {
                        m = 12;
                    } else {
                        m = m - 6;
                    }
                    //计算当年提醒时间
                    String tmp = String.valueOf(nYear) + "-" + String.valueOf(m) + "-" + DateUtils.getTimeStr(dTmp, "dd");
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    Calendar c = Calendar.getInstance();
                    c.setTime(sdf.parse(tmp));
                    Date ddTmp = c.getTime();
                    c.add(Calendar.DATE, -beferDay);
                    this.checkSaveNianShen(startTime, endTime, companyId, mTmp, ddTmp, c, nMonth);
                }
            }
            //退出判断
            if (res == null || res.size() < pcount) {
                break;
            }
            i++;
        }
        return nums;
    }

    /**
     * 保存之前先检测任务日期
     *
     * @param startTime
     * @param endTime
     * @param companyId
     * @param mTmp
     * @param dTmp
     * @param c
     * @param nMonth
     * @throws Exception
     */
    private void checkSaveNianShen(String startTime, String endTime, String companyId, Map mTmp, Date dTmp, Calendar c, int nMonth) throws Exception {
        String expectDate = DateUtils.getTimeStr(c.getTime(), "yyyy-MM-dd");
        if (!"".equals(startTime) && !"".equals(endTime)) {
            boolean timeFlag = true;
            if (!"".equals(startTime) && !"".equals(endTime)) {
                timeFlag = DateUtils.checkTime(expectDate, startTime, endTime);
            }
            if (timeFlag) {
                this.saveNianShenTask(companyId, mTmp, dTmp, expectDate);
            }
        } else {
            // 判断是否该月提醒
            int m = c.get(Calendar.MONTH) + 1;
            if (m == nMonth) {//提醒日期在本月
                TCrmTask task = new TCrmTask();
                this.saveNianShenTask(companyId, mTmp, dTmp, expectDate);
            }
        }
    }

    /**
     * 保存年审任务，先判断是否重复，在进行保存
     *
     * @param companyId
     * @param mTmp
     * @param dTmp
     * @param expectDate
     * @throws Exception
     */
    private void saveNianShenTask(String companyId, Map mTmp, Date dTmp, String expectDate) throws Exception {
        boolean flag = taskDao.checkSbTask(companyId, mTmp.get("id").toString(), "ns", "ga", expectDate);
        if (flag) {
            return;
        }
        TCrmTask task = new TCrmTask();
        task.setCreateTime(new Date().getTime());
        task.setCompanyId(companyId);
        task.setVIN(StringUtil.formatNull(mTmp.get("vin")));
        task.setCarId(StringUtil.formatNull(mTmp.get("id")));
        task.setPlateNum(StringUtil.formatNull(mTmp.get("plateNum")));
        task.setLinkman(StringUtil.formatNull(mTmp.get("ownerName")));
        task.setLinkphone(StringUtil.formatNull(mTmp.get("contactTel")));
        task.setExpectDate(expectDate);
        task.setStatus("0");
        task.setIsBack("0");
        task.setTypePCode("ga");
        task.setTypeCode("ns");
        task.setContent(String.format("%s的车,车主[%s]，将于%s进行年审", task.getPlateNum(), task.getLinkman(), DateUtils.getTimeStr(dTmp, "MM-dd")));
        this.taskDao.saveOrUpdate(task);
    }


    /**
     * 判断从质保开始15年内的车辆数据
     *
     * @param lTmp
     * @param nYear
     * @param nMonth
     * @return
     * @throws Exception
     */
    private boolean checkExpectDate(Long lTmp, int nYear, int nMonth) throws Exception {
        Date dTmp = new Date(lTmp);
        //处理当年具体提醒时间
        String tmp = String.valueOf(nYear) + "-" + DateUtils.getTimeStr(dTmp, "MM-dd");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar c = Calendar.getInstance();
        c.setTime(sdf.parse(tmp));
        c.add(Calendar.DATE, -30);
        // 判断是否该月提醒
        int m = c.get(Calendar.MONTH) + 1;
        if (m == nMonth) {//提醒日期在本月
            return true;
        } else {
            return false;
        }
    }

    /**
     * 保存车辆保险任务信息
     *
     * @param datas
     * @param companyId 公司ID
     * @param tpl       待格式化内容
     * @param pCode     大类
     * @param code      小类
     */
    private Integer baoxianSaveTask(List<Map<String, Object>> datas, String companyId, String tpl, String pCode, String code, String startTime, String endTime) throws Exception {
        Integer nums = 0;
        if (datas.size() > 0) {
            List<TCrmTask> list = new ArrayList<>();
            TCrmTask task;
            for (Map<String, Object> data : datas) {
                if (data.get("vin") == null && data.get("plate_num") == null) {
                    continue;
                }
                if (data.get("txrq") == null) {
                    System.out.println("");
                }
                String checkVin = data.get("vin") == null ? "" : data.get("vin").toString();
                String checkTxrq = data.get("txrq") == null ? "" : data.get("txrq").toString();
                boolean flag = taskDao.checkTaskByVinAndId(companyId, checkVin, StringUtil.formatEmpty(data.get("user_carid")), code, pCode, checkTxrq);
                if (!flag) {
                    boolean timeFlag = true;
                    if (!"".equals(startTime) && !"".equals(endTime)) {
                        timeFlag = DateUtils.checkTime(checkTxrq, startTime, endTime);
                    }
                    if (timeFlag) {
                        task = new TCrmTask();
                        task.setCreateTime(new Date().getTime());
                        task.setCompanyId(companyId);
                        if (data.get("vin") != null) {
                            task.setVIN(StringUtil.formatNull(data.get("vin").toString()));
                        }
                        task.setCarId(StringUtil.formatNull(data.get("user_carid")));
                        if (data.get("plate_num") != null) {
                            task.setPlateNum(StringUtil.formatNull(data.get("plate_num")));
                        } else {
                            if (data.get("vin") != null) {
                                String vin = String.valueOf(data.get("vin"));
                                String plateNum = vin.substring(vin.length() - 6, vin.length());
                                data.put("plate_num", plateNum);
                                task.setPlateNum(plateNum);
                            }
                        }
                        //取保险公司
                        String bxgs = "";
                        if (code.contains("交强")) {
                            bxgs = data.get("compulsory_company") == null ? "" : data.get("compulsory_company").toString();
                        } else {
                            bxgs = data.get("commercial_company") == null ? "" : data.get("commercial_company").toString();

                        }

                        task.setLinkman(StringUtil.formatNull(data.get("user_name")));
                        task.setLinkphone(StringUtil.formatNull(data.get("user_tel")));
                        task.setExpectDate(StringUtil.formatNull(data.get("txrq")));
                        task.setStatus("0");
                        task.setIsBack("0");
                        task.setTypePCode(pCode);
                        task.setTypeCode(code);
                        task.setContent(String.format(tpl, task.getPlateNum(), task.getLinkman(), data.get("dqrq").toString(), bxgs));
                        list.add(task);
                    }
                } else {
                    continue;
                }
            }
            nums += list.size();
            crmCarMaintainTaskDao.insertMaintailLists(list);
        }
        return nums;
    }

    @Override
    public Integer ciNianJiaoQiang(String companyId, String startTime, String endTime) throws Exception {
        String tpl = "%s的车主[%s]将于%s[%s]交强险到期";
        String pcode = "bxcn";
        String code = "cnj";
        Integer beferDay = taskDao.beforDays(companyId, code, pcode + "_" + code);
        if (beferDay != null) {
            List<Map<String, Object>> datas = this.taskDao.baoxian(companyId, beferDay.intValue(), "新保", 0, ROOTBXCODE, code);
            return baoxianSaveTask(datas, companyId, tpl, ROOTBXCODE, code, startTime, endTime);
        }
        return 0;
    }

    @Override
    public Integer ciNianShangYe(String companyId, String startTime, String endTime) throws Exception {
        String tpl = "%s的车主[%s]将于%s[%s]商业险到期";
        String pcode = "bxcn";
        String code = "cns";
        Integer beferDay = taskDao.beforDays(companyId, code, pcode + "_" + code);
        if (beferDay != null) {
            List<Map<String, Object>> datas = this.taskDao.baoxian(companyId, beferDay.intValue(), "新保", 1, ROOTBXCODE, code);
            return baoxianSaveTask(datas, companyId, tpl, ROOTBXCODE, code, startTime, endTime);
        }
        return 0;
    }

    @Override
    public Integer xuBaoShangYe(String companyId, String startTime, String endTime) throws Exception {
        String tpl = "%s的车主[%s]将于%s[%s]商业险到期";
        String pcode = "bxxb";
        String code = "xbs";
        Integer beferDay = taskDao.beforDays(companyId, code, pcode + "_" + code);
        if (beferDay != null) {
            List<Map<String, Object>> datas = this.taskDao.baoxian(companyId, beferDay.intValue(), "续保", 1, ROOTBXCODE, code);
            return baoxianSaveTask(datas, companyId, tpl, ROOTBXCODE, code, startTime, endTime);
        }
        return 0;
    }

    @Override
    public Integer xuBaoJiaoQiang(String companyId, String startTime, String endTime) throws Exception {
        String tpl = "%s的车主[%s]将于%s[%s]交强险到期";
        String pcode = "bxxb";
        String code = "xbj";
        Integer beferDay = taskDao.beforDays(companyId, code, pcode + "_" + code);
        if (beferDay != null) {
            List<Map<String, Object>> datas = this.taskDao.baoxian(companyId, beferDay.intValue(), "续保", 0, ROOTBXCODE, code);
            return baoxianSaveTask(datas, companyId, tpl, ROOTBXCODE, code, startTime, endTime);
        }
        return 0;
    }

    @Override
    public Integer jieDaiShangYe(String companyId, String startTime, String endTime) throws Exception {
        String tpl = "%s的车主[%s]将于%s[%s]商业险到期";
        String pcode = "bxjd";
        String code = "jds";
        Integer beferDay = taskDao.beforDays(companyId, code, pcode + "_" + code);
        if (beferDay != null) {
            List<Map<String, Object>> datas = this.taskDao.baoxian(companyId, beferDay.intValue(), "接待", 1, ROOTBXCODE, code);
            return baoxianSaveTask(datas, companyId, tpl, ROOTBXCODE, code, startTime, endTime);
        }
        return 0;
    }

    @Override
    public Integer jieDaiJiaoQiang(String companyId, String startTime, String endTime) throws Exception {
        String tpl = "%s的车主[%s]将于%s交强险到期";
        String pcode = "bxjd";
        String code = "jdj";
        Integer beferDay = taskDao.beforDays(companyId, code, pcode + "_" + code);
        if (beferDay != null) {
            List<Map<String, Object>> datas = this.taskDao.baoxian(companyId, beferDay.intValue(), "接待", 0, ROOTBXCODE, code);
            return baoxianSaveTask(datas, companyId, tpl, ROOTBXCODE, code, startTime, endTime);
        }
        return 0;
    }

    /**
     * @param companyId
     * @param startTime 2017-07-01
     * @param endTime   2017-07-01
     * @return
     * @throws Exception
     */
    @Override
    public Integer baoXianHuoYue(String companyId, String startTime, String endTime) throws Exception {
        String tpl = "%s的车主[%s]%s挂牌，保险活跃用户";
        String pcode = "bxhy";
        String code = "hyt";
        Integer beferDay = taskDao.beforDays(companyId, code, pcode + "_" + code);
        if (beferDay != null) {
            List<Map<String, Object>> datas = this.taskDao.baoXianHuoYue(companyId, beferDay.intValue(), ROOTBXCODE, code);
            return saveBaoXianFAndH(datas, companyId, tpl, ROOTBXCODE, code, startTime, endTime);
        }
        return 0;
    }

    @Override
    public Integer baoXianFeiHuoYue(String companyId, String startTime, String endTime) throws Exception {
        String tpl = "%s的车主[%s]%s挂牌，保险非活跃用户";
        String pcode = "bxfhy";
        String code = "fhyt";
        Integer beferDay = taskDao.beforDays(companyId, code, pcode + "_" + code);
        if (beferDay != null) {
            List<Map<String, Object>> datas = this.taskDao.baoXianFeiHuoYue(companyId, beferDay.intValue(), ROOTBXCODE, code);
            return saveBaoXianFAndH(datas, companyId, tpl, ROOTBXCODE, code, startTime, endTime);
        }
        return 0;
    }

    private Integer saveBaoXianFAndH(List<Map<String, Object>> datas, String companyId, String tpl, String pCode, String code, String startTime, String endTime) throws Exception {
        int nYear = Integer.valueOf(DateUtils.getTimeStr("yyyy"));
        Integer nums = 0;
        if (datas.size() > 0) {
            List<TCrmTask> tasks = new ArrayList<>();
            for (Map<String, Object> data : datas) {
                String txrq = data.get("txrq").toString();
                String txrqDate = String.valueOf(nYear) + "-" + txrq;
                String dqrq = data.get("dqrq").toString();
                String dqrqDate = String.valueOf(nYear) + "-" + dqrq;
                //如果提醒日期>到期日期，则到期日期增加一年
                if (txrq.compareTo(dqrq) > 0) {
                    dqrqDate = String.valueOf(nYear + 1) + "-" + dqrq;
                }
                //判断是否存在相同的任务
                boolean flag = taskDao.checkSbTask(companyId, data.get("vin").toString(), code, pCode, txrqDate);
                if (!flag) {
                    boolean timeFlag = true;
                    if (!"".equals(startTime) && !"".equals(endTime)) {
                        //判断任务是否在预生成的区间内
                        timeFlag = DateUtils.checkTime(txrqDate, startTime, endTime);
                    }
                    if (timeFlag) {
                        TCrmTask task = new TCrmTask();
                        task.setCompanyId(companyId);
                        task.setCreateTime(new Date().getTime());
                        if (data.get("vin") != null) {
                            task.setVIN(StringUtil.formatNull(data.get("vin").toString()));
                        }
                        task.setCarId(StringUtil.formatNull(data.get("id")));
                        if (data.get("plate_num") != null) {
                            task.setPlateNum(StringUtil.formatNull(data.get("plate_num")));
                        } else {
                            if (data.get("vin") != null) {
                                String vin = String.valueOf(data.get("vin"));
                                String plateNum = vin.substring(vin.length() - 6, vin.length());
                                data.put("plate_num", plateNum);
                            }
                        }
                        //联系人
                        String contactName = "";
                        if (data.get("contact_name") != null) {
                            contactName = String.valueOf(data.get("contact_name"));
                        } else if (data.get("owner_name") != null) {
                            contactName = String.valueOf(data.get("owner_name"));

                        }
                        task.setLinkman(contactName);
                        //联系电话
                        String contactTel = "";
                        if (data.get("contact_tel") != null) {
                            contactTel = String.valueOf(data.get("contact_tel"));
                        } else if (data.get("owner_tel") != null) {
                            contactTel = String.valueOf(data.get("owner_tel"));
                        }
                        task.setLinkphone(contactTel);

                        task.setExpectDate(txrqDate);
                        task.setStatus("0");
                        task.setTypePCode(pCode);
                        task.setTypeCode(code);
                        task.setContent(String.format(tpl, task.getPlateNum(), task.getLinkman(), dqrq));
                        tasks.add(task);
                        System.out.println("增加" + task.getTypeCode() + "任务：" + task.getVIN());
                    }
                }
            }
            nums += tasks.size();
            crmCarMaintainTaskDao.insertMaintailLists(tasks);
        }
        return nums;
    }


    //------------------------------------------------------------------------------------------------------------------

    /**
     * 任务查询新版
     *
     * @param uid
     * @param start
     * @param limit
     * @param status
     * @param vin
     * @param title
     * @param pcode
     * @param code
     * @param expectDate
     * @return
     * @throws Exception
     */
    @Override
    public Map crmCarTaskQuery(String uid, String start, String limit, String status, String vin, String title, String pcode,
                               String code, String expectDate, String area) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        //只有start为零的时候才去更新task_nums
        this.updateCrmCarTaskNums(uid, companyId, start);
        StringBuffer sql = new StringBuffer();
        List<String> params = new ArrayList<>();
        sql.append(" select * from ( ");
        sql.append(" SELECT " +
                " crmtask.*,tuser.u_name,paramDef.`name`,crmtask.task_nums as cp,");
        sql.append("case when crmtask.type_code='zdy' then '自定义' else paramDef.`name`  end    typeName,");
        sql.append("CASE " +
                "WHEN crmtask.`status`='0' THEN '未完成' " +
                "WHEN crmtask.`status`='1' THEN '已完成' " +
                "WHEN crmtask.`status`='2' THEN '关闭' " +
                "ELSE '' END statusName ");
        sql.append(" FROM ");
        sql.append(" (select * from t_crm_task_real task ");
        sql.append(" where  task.company_id = ? ");
        params.add(companyId);
        //标题查询
        if (!StringUtils.isEmpty(title)) {
            sql.append("and task.content like ? ");
            params.add("%" + title + "%");
        }
        //类型大类
        if (!StringUtils.isEmpty(pcode)) {
            sql.append(" AND task.type_pcode like ?  ");
            params.add(pcode + "%");
        }
        //类型小类
        if (!StringUtils.isEmpty(code)) {
            if ("bx".equals(pcode)) {
                sql.append(" AND task.type_code like  ?");
                params.add(code + "%");
            } else {
                sql.append(" AND task.type_code = ?");
                params.add(code);
            }
        }
        //指定哪一天
        if (!StringUtils.isEmpty(expectDate)) {
            sql.append(" AND task.expect_date=? ");
            params.add(expectDate);
        } else {//未指定哪一天 查询今天之前的
            sql.append(" and task.expect_date <= ? ");
            params.add(DateUtils.getTimeStr("yyyy-MM-dd"));
        }

        //状态分0，1，2，3，4其中0，1，2与数据库记录状态一致，3为延期状态，4为非延期状态
        if (!StringUtils.isEmpty(status)) {
            sql.append(" and task.status = ? ");
            params.add(status);
        } else {
            sql.append(" and task.status = '0' ");
        }
        if (!StringUtils.isEmpty(vin)) {
            sql.append(" and task.vin like ? ");
            params.add("%" + vin + "%");
        }
        sql.append(" and task.sa_id=? ");
        params.add(uid);
        sql.append(" ) crmtask " +
                " LEFT JOIN t_bc_user tuser ON crmtask.sa_id = tuser.id " +
                " LEFT JOIN t_bc_param_def paramDef ON crmtask.type_code=paramDef.`code` ");
        //增加按行政区域查询车辆任务信息
        if (!"".equals(area)) {
            sql.append(" LEFT JOIN (select * from t_crm_car where company_id=? ) car ON crmtask.car_id=car.id and crmtask.vin=car.vin ");
            params.add(companyId);
            String areaStr = area.toString().replace(",", "|").replace("[", "").
                    replace(" ", "").replace("]", "").replace("\"", "");
            if (areaStr.length() > 0) {
                sql.append(" where car.owner_city regexp ? ");
                params.add(areaStr);
            }
        }
        //未输入title时按VIN分组
        if (StringUtils.isEmpty(title)) {
            sql.append(" group by crmtask.vin ");
        }
        sql.append(" order by crmtask.expect_date asc,crmtask.type_pcode desc ");
        sql.append(" ) temp ");
        return this.taskDao.page(sql.toString(), params.toArray(), start, limit);
    }

    /**
     * 更新车辆任务对应的任务数
     * 根据VIN,SAID来更新
     *
     * @param saId
     * @param companyId
     * @param start
     * @throws Exception
     */
    @Override
    public void updateCrmCarTaskNums(String saId, String companyId, String start) throws Exception {
        if ("0".equals(start)) {
            String sql = " update  t_crm_task_real  task, " +
                    " (SELECT COUNT(id) task_nums,vin,car_id,sa_id from t_crm_task_real where company_id=? and sa_id=? " +
                    " and `status`='0' group by vin,car_id) tempTask" +
                    " set task.task_nums=tempTask.task_nums" +
                    " where task.vin=tempTask.vin and task.car_id=tempTask.car_id AND task.sa_id=tempTask.sa_id " +
                    " and task.company_id=? and task.sa_id =?  and task.`status`='0'";
            this.crmCarMaintainTaskDao.executeSQL(sql, companyId, saId, companyId, saId);
        }
    }

    /**
     * 1、当天 当月任务，只查询处理人是自己的任务
     */
    @Override
    public Map dayAndMonthQueryTask(String start, String limit, String companyId, String pcode, String code, String status, String uid,
                                    String day, String month, String platenum, String title, String vin, String area, String queryExpecDate) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
//        List<Map<String, Object>> datas = this.carRefDao.findCarRefByUid(companyId, uid);
        List<String> ps = new ArrayList<>();
        sqlStr.append("select  car2.owner_name , car2.owner_tel ,temp2.id,temp2.car_id,concat('第',temp2.task_count,'次',temp2.content) content ,temp2.expect_date,temp2.deal_date,temp2.plateNum,temp2.order_num orderNum,temp2.type_code,temp2.type_pcode," +
                "temp2.cp,temp2.vin, tuser.u_name uname,temp2.`status`,temp2.assign_id as assignId,temp2.assign_name as assignName,temp2.sa_id as saId, temp2.store_area_code,temp2.store_p_area_code," +
                "case when temp2.type_code='zdy' then '自定义'" +
                "    else paramDef.`name`                " +
                "    end     " +
                " typeName," +
                "");
        sqlStr.append("CASE " +
                "WHEN temp2.`status`='0' THEN '未完成' " +
                "WHEN temp2.`status`='1' THEN '已完成' " +
                "WHEN temp2.`status`='2' THEN '关闭' " +
                "ELSE '' END statusName ");
        sqlStr.append("from ( ");


        if (!"".equals(platenum)) {
            sqlStr.append("SELECT  ");
        } else {
            sqlStr.append("SELECT  DISTINCT ");
        }
        sqlStr.append("  crmtask.* ," +
                "ct.cp as cp ");
        sqlStr.append("FROM  ");
        sqlStr.append(
//                " t_crm_car ," +
                "t_crm_task_real crmtask LEFT JOIN ");
        //---------------------计算任务数量--------------------
        if (!"".equals(platenum)) {
            sqlStr.append("(select 1 as cp,crmtask.plateNum,crmtask.vin  from t_crm_task_real crmtask where crmtask.company_id=? ");
            ps.add(companyId);
        } else {
            sqlStr.append(" (select COUNT(crmtask.id) as cp,crmtask.plateNum,crmtask.vin  from t_crm_task_real crmtask where crmtask.company_id=? ");
            ps.add(companyId);
        }
        //计算数量 也需要增加时间日期的计算
        if (!StringUtils.isEmpty(day) && day.length() > 10) {
            sqlStr.append(" and crmtask.expect_date <= ?");
            ps.add(day.substring(0, 10));
        } else if (!StringUtils.isEmpty(month) && month.length() > 7) {
            sqlStr.append(" and crmtask.expect_date like ?");
            ps.add(month.substring(0, 7) + "%");
        }

        //根据 UID 查询车辆分配规则
        this.carDayAndMonthSql(sqlStr, ps, status, uid, day, month, vin);
        sqlStr.append("  group by crmtask.vin ");
        sqlStr.append(") as ct");
        //---------------------计算任务数量结束------------------
        sqlStr.append(" on ct.vin = crmtask.vin ");
        sqlStr.append("WHERE  ");
        sqlStr.append("  crmtask.company_id = ? ");
        ps.add(companyId);
        if (!StringUtils.isEmpty(title)) {
            sqlStr.append("and ( crmtask.content like ? or crmtask.plateNum like ? ) ");
            ps.add("%" + title + "%");
            ps.add("%" + title + "%");
        }
        //类型大类
        if (!StringUtils.isEmpty(pcode)) {
            sqlStr.append(" AND crmtask.type_pcode like ?  ");
            ps.add(pcode + "%");
        }
        if (!StringUtils.isEmpty(platenum)) {
            sqlStr.append(" AND crmtask.plateNum = ?");
            ps.add(platenum);
        }
        //类型小类
        if (!StringUtils.isEmpty(code)) {
            if ("bx".equals(pcode)) {
                sqlStr.append(" AND crmtask.type_code like  ?");
                ps.add(code + "%");
            } else {
                sqlStr.append(" AND crmtask.type_code = ?");
                ps.add(code);
            }
        }
        //根据 UID 查询车辆分配规则
        this.carDayAndMonthSql(sqlStr, ps, status, uid, day, month, vin);

        if (!StringUtils.isEmpty(day) && day.length() >= 10) {
            sqlStr.append(" and crmtask.expect_date <= ?");
            ps.add(day.substring(0, 10));
        } else if (!StringUtils.isEmpty(month) && month.length() >= 7) {
            sqlStr.append(" and crmtask.expect_date like ?");
            ps.add(month.substring(0, 7) + "%");
        }
        if (!StringUtils.isEmpty(queryExpecDate) && queryExpecDate.length() > 7) {
            sqlStr.append(" and crmtask.expect_date = ?");
            ps.add(queryExpecDate);
        }

        if (StringUtils.isEmpty(title)) {
            sqlStr.append(" group by crmtask.vin ");
        }
        sqlStr.append(" order by crmtask.expect_date asc,crmtask.plateNum asc ");
        sqlStr.append(" ) as temp2  " +
                " LEFT JOIN t_bc_user tuser ON temp2.sa_id=tuser.id " +
                " LEFT JOIN t_bc_param_def paramDef ON temp2.type_code=paramDef.`code` ");
        //增加按行政区域查询车辆任务信息
        if (!"".equals(area)) {
            sqlStr.append(" LEFT JOIN t_crm_car car on temp2.vin=car.vin ");
            String areaStr = area.toString().replace(",", "|").replace("[", "").replace(" ", "").replace("]", "").replace("\"", "");
            if (areaStr.length() > 0) {
                sqlStr.append(" where car.owner_city regexp ? ");
                ps.add(areaStr);
            }
        }
        /****增加车辆信息关联***/
        sqlStr.append(" LEFT JOIN t_crm_car car2 on temp2.vin=car2.vin and temp2.car_id=car2.id and car2.company_id = '" + companyId + "' ");
        /****增加车辆信息关联***/

        sqlStr.append(" order by temp2.expect_date asc,temp2.plateNum asc,temp2.type_pcode asc");
        //TODO:测试打印sql语句和参数信息
        System.out.println(sqlStr.toString());
        for (int i = 0; i < ps.toArray().length; i++) {
            System.out.println(ps.toArray()[i]);
        }
        return this.taskDao.page(sqlStr.toString(), ps.toArray(), start, limit);
    }

    /**
     * 生成sql信息
     *
     * @param sqlStr
     * @param ps
     * @param status
     * @param uid
     * @param day
     * @param month
     * @throws Exception
     */
    private void carDayAndMonthSql(StringBuffer sqlStr, List<String> ps, String status, String uid, String day, String month, String vin) throws Exception {

        //状态分0，1，2，3，4其中0，1，2与数据库记录状态一致，3为延期状态，4为非延期状态
        if (!StringUtils.isEmpty(status)) {
            if (status.equals("3")) {//延期,提醒日期小于当前日期
                sqlStr.append(" and crmtask.expect_date < ? and crmtask.status=0 ");
                ps.add(DateUtils.getTimeStr("yyyy-MM-dd"));
            } else if (status.equals("4")) {//未延期，提醒日期等于当前日期
                if (!StringUtils.isEmpty(day) && day.length() > 10) {
                    sqlStr.append(" and crmtask.expect_date = ? and crmtask.status=0 ");
                    ps.add(DateUtils.getTimeStr("yyyy-MM-dd"));
                } else if (!StringUtils.isEmpty(month) && month.length() > 7) {
                    sqlStr.append(" and crmtask.expect_date >= ? and crmtask.status=0 ");
                    ps.add(DateUtils.getTimeStr("yyyy-MM-dd"));
                }
            } else {
                if (!status.equals("5")) {
                    sqlStr.append(" and crmtask.status = ?");
                    ps.add(status);
                }
            }
        } else {
            sqlStr.append(" and crmtask.status = '0' ");
        }
        if (!StringUtils.isEmpty(vin)) {
            sqlStr.append(" and crmtask.vin like ? ");
            ps.add("%" + vin + "%");
        }
        sqlStr.append(" and crmtask.sa_id=? ");
        ps.add(uid);

    }

    //获取部门信息，用于过滤门店
    private Map getOrganInfo(String uid) throws Exception{
        String deptId = TokenUtil.getDeptId(uid);
        String sql = "select * from t_bc_organ where id = ? ";
        List params = new ArrayList();
        params.add(deptId);
        Map info = this.taskDao.info(sql,params.toArray());
        Map map = new HashMap();
        map.put("oName",info.get("oName"));
        map.put("oPId",info.get("oPId"));
        return map;
    }

    //------------------------------------------------------------------------------------------------------------------

    /**
     * @param start
     * @param limit
     * @param companyId
     * @param pcode
     * @param code
     * @param uid
     * @param title
     * @param deal
     * @param type
     * @param allotstatus
     * @param assignId
     * @param allotType
     * @param allotTypeV
     * @param taskType
     * @param month
     * @param series
     * @param acTag
     * @return
     * @throws Exception
     */
    @Override
    public Map queryMangerPage(String start, String limit, String companyId, String pcode, String code, String uid,
                               String title, String deal, int type, String allotstatus,
                               String assignId, String allotType, String allotTypeV, String taskType, String month, String series, String acTag, String inWx,Map belongsStoreName
    ) throws Exception {
      Map organInfo = this.getOrganInfo(uid);


        List nameList = (List) belongsStoreName.get("belongsStoreName");
        String namesSQL = "";
        for (int i = 0 ;i<nameList.size();i++){
            namesSQL += "'"+nameList.get(i)+"'";
            if(i != nameList.size()-1){
                namesSQL += ", ";
            }
        }
        System.out.println(namesSQL);

        StringBuffer sqlStr = new StringBuffer();
        List<String> ps = new ArrayList<>();
        sqlStr.append("select temp2.is_back, temp2.deal_date,temp2.id,temp2.car_id,concat('第',temp2.task_count,'次',temp2.content) as content,temp2.expect_date,temp2.plateNum,temp2.type_code,temp2.type_pcode,temp2.cp,temp2.vin, " +
                "tuser.u_name uname,temp2.`status`,temp2.assign_id as assignId,temp2.assign_name as assignName,temp2.sa_id as saId,");
        sqlStr.append("d.u_name as saName,");
        sqlStr.append(
                "car.owner_name,car.owner_tel, " +
                        "case when temp2.type_code='zdy' then '自定义'" +
                        "    else paramDef.`name`                " +
                        "    end     " +
                        " typeName," +
                        "");
        sqlStr.append("CASE " +
                "WHEN temp2.`status`='0' THEN '未完成' " +
                "WHEN temp2.`status`='1' THEN '已完成' " +
                "WHEN temp2.`status`='2' THEN '关闭' " +
                "ELSE '' END statusName ");
        sqlStr.append("from ( ");

        sqlStr.append("SELECT  DISTINCT ");
        sqlStr.append("  crmtask.* ," +
                "ct.cp as cp ");
        sqlStr.append("FROM  ");
        sqlStr.append(
//                " t_crm_car ," +
                "t_crm_task_real crmtask LEFT JOIN ");
        //---------------------计算任务数量--------------------
        sqlStr.append(" (select COUNT(crmtask.id) as cp,crmtask.plateNum,crmtask.vin  from t_crm_task_real crmtask where crmtask.company_id=? ");
        ps.add(companyId);
        //根据 UID 查询车辆分配规则
        this.carRefSql(companyId, uid, sqlStr, ps, type, allotstatus, assignId, deal, allotType, allotTypeV, taskType);
        sqlStr.append("  group by crmtask.vin ");
        sqlStr.append(") as ct");
        //---------------------计算任务数量结束------------------
        sqlStr.append(" on ct.vin = crmtask.vin ");
        sqlStr.append("WHERE  ");
        sqlStr.append("  crmtask.company_id = ? ");
        ps.add(companyId);
        if (!StringUtils.isEmpty(title)) {
            sqlStr.append("and ( crmtask.content like ? or crmtask.plateNum like ? ) ");
            ps.add("%" + title + "%");
            ps.add("%" + title + "%");
        }

        //类型大类
        if (!StringUtils.isEmpty(pcode)) {
            sqlStr.append(" AND crmtask.type_pcode like ?  ");
            ps.add(pcode + "%");
        }
        //类型小类
        if (!StringUtils.isEmpty(code)) {
            if ("bx".equals(pcode)) {
                sqlStr.append(" AND crmtask.type_code like  ? ");
                ps.add(code + "%");
            } else {
                sqlStr.append(" AND crmtask.type_code = ?");
                ps.add(code);
            }

        }

        if(nameList.size()>0){
            sqlStr.append(" AND crmtask.store_name IN ("+namesSQL+") ");
        }
        if(organInfo != null){
            if(organInfo.get("oPId").equals("8a9aa28460026f310160059c8dd804b2")){ // 如果是所属连锁店的门店，则只能看本店的任务
                sqlStr.append("AND crmtask.store_name = ? ");
                ps.add(organInfo.get("oName").toString());
            }
        }


        // 根据日期查询
        if (!StringUtils.isEmpty(month)) {
            sqlStr.append(" AND left(crmtask.expect_date,7)=?");
            ps.add(month);
        }

        //根据 UID 查询车辆分配规则
        this.carRefSql(companyId, uid, sqlStr, ps, type, allotstatus, assignId, deal, allotType, allotTypeV, taskType);
        sqlStr.append(" order by crmtask.expect_date asc ");
        sqlStr.append(") as temp2  " +
                "LEFT JOIN t_bc_user tuser ON temp2.sa_id=tuser.id " +
                "LEFT JOIN t_bc_param_def paramDef ON temp2.type_code=paramDef.`code`");
        //角色ID
//        sqlStr.append(" left join `t_crm_car_conf_detail` co on co.vin= temp2.vin and co.company_id=temp2.company_id and co.`role_id` in('402881895bb23f8c015bb2427d500002') " );
        sqlStr.append("LEFT JOIN (" +
                " SELECT co.*, u.u_name FROM ( SELECT * FROM t_crm_car_conf_detail co WHERE co.role_id = ? AND co.company_id = ? " +
                " group by vin,company_id ) co, t_bc_user u WHERE co.u_id = u.id group by co.vin " +
                " ) d ON temp2.vin = d.vin AND temp2.company_id = d.company_id ");
        ps.add(Constant.SA_ROLE_ID);
        ps.add(companyId);
//        sqlStr.append(" left join t_bc_user cou on co.`u_id`= cou.id " );
        sqlStr.append(" left join t_crm_car car on  car.vin= temp2.vin and car.company_id=temp2.company_id ");


        //------------------------- 增加任务大类名称显示----By术洪超--2017.03.22  16:00----------------------//
        /***增加车系-活动标签**/
        StringBuffer tempBuf = new StringBuffer();
        if (!StringUtils.isEmpty(series)) {
            tempBuf.append(" AND LOCATE(?,car.series) ");
            ps.add(series);
        }
        /**仅针对保险任务-查询车辆等级标签**/
        /***
         * 0:活跃车辆
         * 1：非活跃车辆
         * 2：在保不在修车辆
         * 3：在修不在保车辆
         */
        if ("bx".equals(pcode)) {
            switch (acTag) {
                case "0":
                    //活跃车辆
                    tempBuf.append(" AND car.active in( 'B','A','SS','SSS' ) ");
                    //ps.add(acTag);
                    break;
                case "1":
                    //非活跃车辆
                    tempBuf.append(" AND car.active not in( 'B','A','SS','SSS' ) ");
                    break;
                case "3":
                    //在保不在修车辆，无一年内有效进厂工单
                    tempBuf.append(" AND temp2.type_code not in ( 'hyt','fhyt') AND NOT EXISTS( select 1 from t_crm_car_workorder wo where wo.vin = car.vin and wo.company_id = car.company_id and from_unixtime(balance_date/1000,'%Y-%m-%d') between ? and ?  and wo.real_amount > '0.00' ) ");
                    //一年前
                    ps.add(DateUtils.oneYearAge());
                    //今天
                    ps.add(DateUtils.getTimeStr("yyyy-MM-dd"));

                    break;
                case "2":
                    //在修不在保车辆
                    tempBuf.append(" AND temp2.type_code in ( 'hyt','fhyt') ");
                    break;
                default:
                    break;
            }
        }
//        if (!StringUtils.isEmpty(acTag)) {
//            tempBuf.append(" AND car.active= ? ");
//            ps.add(acTag);
//        }
        if (tempBuf.length() > 5) {
            sqlStr.append(" WHERE " + tempBuf.toString().replaceFirst("AND", ""));
        }
        sqlStr.append("    order by temp2.expect_date asc ");
        //TODO:测试打印sql语句和参数信息
        System.out.println(sqlStr.toString());
        for (int i = 0; i < ps.toArray().length; i++) {
            System.out.println(ps.toArray()[i]);
        }
        return this.taskDao.page(sqlStr.toString(), ps.toArray(), start, limit);
    }

    /**
     * 生成查询语句
     *
     * @param companyId
     * @param uid
     * @param sqlStr
     * @param ps
     * @param type
     * @throws Exception
     */
    private void carRefSql(String companyId, String uid, StringBuffer sqlStr, List<String> ps,
                           int type, String allotstatus, String assignId, String deal, String allotType, String allotTypeV, String taskType) throws Exception {
        if (!"".equals(assignId)) {
            sqlStr.append("and crmtask.assign_id= ? ");
            ps.add(assignId);
        } else if (!"".equals(deal)) {
            sqlStr.append("and crmtask.sa_id = ? ");
            ps.add(deal);
        }
        //不是管理员是需要增加分配规则条件
        //管理员角色可以不遵守
        boolean isManger = TokenUtil.isCompanyAdmin(uid);
        if (!isManger) {
            if ("".equals(assignId) && "".equals(deal)) {//除管理员角色外，其它角色需要按分配权限来查询
                this.roleAndCar(uid, companyId, type, sqlStr, ps);
            }
        }
        /*任务管理--任务分配--未分配0 已分配1 ，判断未分配与已分配 按said是否分配来*/
        if (!"".equals(allotstatus)) {
            if ("0".equals(allotstatus)) {
                sqlStr.append("AND crmtask.sa_id is null");
            } else {
                sqlStr.append("AND crmtask.sa_id is not null");
            }
        }
        if (!"".equals(taskType)) {
            sqlStr.append(" AND crmtask.status=? ");
            ps.add(taskType);
        }
        if (!"".equals(allotTypeV)) {
            String str[] = allotTypeV.split(",");
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < str.length; i++) {
                sb.append(str[i].toString());
                sb.append(",");
            }
            sb.deleteCharAt(sb.length() - 1);
            //0全部、1 尾号、2 尾数、3 VIN、
            // StringBuffer pdsql = new StringBuffer();
            //生成过滤语句
            if (allotType.equals("1")) {
                sqlStr.append(" AND  crmtask.plateNum REGEXP '[" + sb.toString().toUpperCase().replaceAll(",", "") + "]$'");
            } else if (allotType.equals("2")) {
                sqlStr.append(" AND crmtask.plateNum REGEXP '.*[" + sb.toString().toUpperCase().replaceAll(",", "") + "][A-Za-z]*$'");
            } else if (allotType.equals("3")) {
                sqlStr.append(" AND EXISTS (select * from t_crm_task_car_vin_detail where crmtask.vin=vin and u_id=? and car_vin_id=? and company_id=?)");
                ps.add(uid);
                ps.add(allotTypeV);
                ps.add(companyId);
            }
        }

    }

    private void roleAndCar(String uid, String companyId, int type, StringBuffer sqlStr, List ps) throws Exception {
        List<Map<String, Object>> roles = this.taskDao.findRoleByUid(uid);
        //生成资源
        if (roles != null || roles.size() > 0) {
            List<String> roleIds = new ArrayList<>();
            for (int j = 0; j < roles.size(); j++) {
                Map role = roles.get(j);
                roleIds.add(role.get("r_id").toString());
            }
            if (roleIds.size() > 0) {
                List<Map<String, Object>> manageRoles = taskConfService.getManageTaskTypeByTaskType(uid, roleIds);
                if (manageRoles != null || manageRoles.size() > 0) {
                    sqlStr.append(" and  crmtask.type_code in (");
                    for (int i = 0; i < manageRoles.size(); i++) {
                        Map<String, Object> map = manageRoles.get(i);
                        String typeCCode = String.valueOf(map.get("taskCCode"));
                        sqlStr.append(" '" + typeCCode + "'");
                        sqlStr.append(",");
                    }
                    sqlStr.append("'zdy'");
                    sqlStr.append(" )");
                }
            } else {
                throw new Exception("未分配任务管理权限，请联系管理员!!");
            }
        }


//        List<Map<String, Object>> roles = this.taskDao.findRoleByUid(uid);
//        if (roles != null || roles.size() >= 0) {


//            Map taskTypeMap = new HashMap();
//            for (Map<String, Object> role : roles) {
//                taskTypeMap.putAll(this.taskDao.findCodeByRId(companyId, role.get("r_id").toString(),String.valueOf(type)));
//            }
//            //角色特殊条件过滤
//            if (taskTypeMap.size()>0) {
//                sqlStr.append(" and  crmtask.type_code in (");
//                Object[] keys= taskTypeMap.keySet().toArray();
//                for(int i=0;i<keys.length;i++){
//                    sqlStr.append(" '" + String.valueOf(keys[i]) + "'");
//                    if(i<(keys.length-1)){
//                        sqlStr.append(",");
//                    }
//                }
//                sqlStr.append(")");
//            }
//        }
    }


    //---------------------------以下代码须保留，原因：t_crm_task 中sa_id 生成任务时根据车牌规则存放处理人；以下代码暂时保留-------------------------------------------

//    public Map query(String start, String limit, String companyId, String pcode, String code, String status, String uid, String day, String month, String platenum) throws Exception {
//
//        StringBuffer sqlStr = new StringBuffer();
//        sqlStr.append("select temp2.* from ( ");
//        sqlStr.append("SELECT  DISTINCT ");
//        sqlStr.append("  t_crm_task.* ,t_crm_car.owner_name as carownername,t_crm_car.owner_tel as carowner_tel,t_crm_car.contact_name as carcontact_name,t_crm_car.contact_tel as car_contact_tel,ct.cp as cp ");
//        sqlStr.append("FROM  ");
//        sqlStr.append(" t_crm_car ,t_crm_task ");
//        sqlStr.append("LEFT JOIN (select COUNT(*) as cp,plateNum  from t_crm_task  group by plateNum ) as ct");
//        sqlStr.append(" on ct.plateNum = t_crm_task.plateNum ");
//        sqlStr.append("WHERE  ");
//        sqlStr.append("  t_crm_task.company_id = ? and (t_crm_task.car_id = t_crm_car.id or t_crm_task.plateNum = t_crm_car.plate_num or t_crm_task.vin = t_crm_car.vin)");
//        List<String> ps = new ArrayList<>();
//        ps.add(companyId);
//        //类型大类
//        if (!StringUtils.isEmpty(pcode)) {
//            sqlStr.append(" AND type_pcode = ?  ");
//            ps.add(pcode);
//        }
//        if (!StringUtils.isEmpty(platenum)) {
//            sqlStr.append(" AND t_crm_task.plateNum = ?");
//            ps.add(platenum);
//        }
//        //类型小类
//        if (!StringUtils.isEmpty(code)) {
//            sqlStr.append(" AND type_code = ?");
//            ps.add(code);
//        }
//        //状态分0，1，2，3，4其中0，1，2与数据库记录状态一致，3为延期状态，4为非延期状态
//        if (!StringUtils.isEmpty(status)) {
//            if (status.equals("3")) {//延期,提醒日期小于当前日期
//                sqlStr.append(" and expect_date < ?");
//                ps.add(DateUtils.getTimeStr("yyyy-MM-dd"));
//            } else if (status.equals("4")) {//未延期，提醒日期等于当前日期
//                sqlStr.append(" and expect_date = ?");
//                ps.add(DateUtils.getTimeStr("yyyy-MM-dd"));
//            } else {
//                sqlStr.append(" and t_crm_task.status = ?");
//                ps.add(status);
//            }
//        }
//        //
//        //根据 UID 查询车辆分配规则
//        List<Map<String, Object>> datas = this.carRefDao.findCarRefByUid(companyId, uid);
//        System.out.println(datas.size());
//        if (datas == null || datas.size() == 0) {
//            throw new Exception("该用户未分配车辆");
//        }
//        if (datas.get(0).get("type") == null) {
//            throw new Exception("该用户未分配车辆");
//        }
//        Map<String, Object> has = new HashMap<String, Object>();
//        String tmp = null;
//        String tmps[] = null;
//        //可能存在单个value字段1，2，3样式，需先分割开
//        for (int i = 0; i < datas.size(); i++) {
//            if (datas.get(i).get("value") == null) {
//                continue;
//            }
//            tmp = datas.get(i).get("value").toString();
//            tmps = tmp.split(",");
//            for (int j = 0; j < tmps.length; j++) {
//                has.put(tmps[j], "1");
//            }
//        }
//        Object obj[] = has.keySet().toArray();
//        StringBuffer sb = new StringBuffer();
//        for (int i = 0; i < obj.length; i++) {
//            sb.append(obj[i].toString());
//            //sb.append(",");
//        }
//        sb.deleteCharAt(sb.length() - 1);
//        System.out.println(sb.toString());
//        //0全部、1 尾号、2 尾数、3 VIN、
//        // StringBuffer pdsql = new StringBuffer();
//        //生成过滤语句
//        if (datas.get(0).get("type").toString().equals("1")) {
//            sqlStr.append(" AND  t_crm_task.plateNum REGEXP '[" + sb.toString().replaceAll(",", "") + "]$'");
//        } else if (datas.get(0).get("type").toString().equals("2")) {
//            sqlStr.append(" AND t_crm_task.plateNum REGEXP '.*[" + sb.toString().replaceAll(",", "") + "][A-Za-z]*$'");
//        } else {//全部
//            //分配全部车辆
//        }
//        //根据角色信息判断过滤过滤规则
//        //s1 先查询角色信息，固定角色id
//        //8a1029015a6e4a93015a6eadfe860002 SA角色
//        //8a1029015a6e4a93015a6eaea2790004  客服角色
//        //8a1029015a6e4a93015a6eaf47940006 保险接待角色
//        //其他角色无权限查看数据

//        List<Map<String, Object>> roles = this.taskDao.findRoleByUid(uid);
//        if (roles == null || roles.size() == 0) {
//            throw new Exception("未分配任务处理角色！");
//        }
//
//
//        /*****
//         * 该用户所有的角色之和 查询所分配的任务类型之和，此处需要修改
//         * ****/
//        Map taskTypeMap = new HashMap();
//        for (Map<String, Object> role : roles) {
//            taskTypeMap.putAll(this.taskDao.findCodeByRId(companyId, role.get("r_id").toString()));
//        }
//        if (taskTypeMap.size() == 0) {
//            throw new Exception("未分配任务类型！");
//        }
//        //角色特殊条件过滤
//        if (taskTypeMap.size()>0) {
//            sqlStr.append(" and type_code in (");
//            for(Object key:taskTypeMap.keySet()) {
//
//                sqlStr.append(" '" + String.valueOf(key)+"', ");
//            }
//            sqlStr.append(" '' ) ");
//        }
//        //日期过滤，day为按日过滤，month为按月过滤，二者只有其一
//        if (!StringUtils.isEmpty(day) && day.length() > 10) {
//            sqlStr.append(" and expect_date <= ?");
//            ps.add(day.substring(0, 10));
//        } else if (!StringUtils.isEmpty(month) && month.length() > 7) {
//            sqlStr.append(" and expect_date like ?");
//            ps.add(month.substring(0, 7) + "%");
//        }
//        sqlStr.append(" order by expect_date asc ) as temp2 ");
//        //TODO:测试打印sql语句和参数信息
//        System.out.println(sqlStr.toString());
//        for (int i = 0; i < ps.toArray().length; i++) {
//            System.out.println(ps.toArray()[i]);
//        }
//        return this.taskDao.page(sqlStr.toString(), ps.toArray(), start, limit);
//    }

    //---------------------------以上代码须保留，原因：t_crm_task 中sa_id 生成任务时根据车牌规则存放处理人；以上代码暂时保留-------------------------------------------

    private void findTaskSaNameByPlateNum(List<Map<String, Object>> mapList) {
        for (Map<String, Object> map : mapList) {
            //取出车牌号
            String plateNum = String.valueOf(map.get("plate_num"));
            //取出必号
            String lastStr = plateNum.substring(plateNum.length() - 1, plateNum.length());
            //取出尾数
            String plateNum_n = this.getNumbersByStr(plateNum);
            String lastNum = plateNum_n.substring(plateNum_n.length() - 1, plateNum_n.length());
            //公司ID
            String companyId = String.valueOf(map.get("company_id"));

        }
    }

    private String getNumbersByStr(String plateNum) {
        String regEx = "[^0-9]";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(plateNum);
        return m.replaceAll("").toString();
    }

    /****
     * 数据变更
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public TCrmTask saveOrUpdate(TCrmTask data, String uid) throws Exception {

        this.taskDao.saveOrUpdate(data);
        return data;
    }

    @Override
    public TCrmTaskReal saveTeskRel(TCrmTaskReal data, String uid) throws Exception {
        data.setTypeCode("zdy");
        data.setTypePCode("qt");
        data.setStatus("0");
        data.setSaId(uid);
        data.setCompanyId(TokenUtil.getCompanyId(uid));
        this.taskDao.saveOrUpdate(data);
        return data;
    }


    //-----------------------------------------车辆任务预生成开始--------------------------------

    /**
     * 根据条件查询任务信息
     *
     * @param typeCode  任务小类
     * @param typePCode 任务大类
     * @param title     标题 也就是任务提醒内容
     * @param plateNum  车牌号
     * @param companyId 公司ID
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map searchTask(String typeCode, String typePCode, String title, String plateNum, String companyId,
                          String start, String limit) throws Exception {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("SELECT " +
                "temp2.*, case                  " +
                "    when temp2.type_code='zdy' then '自定义' " +
                "    else paramDef.`name`                " +
                "    end     " +
                " typeName, ");
        sqlStr.append("CASE " +
                "WHEN temp2.`status`='0' THEN '未完成' " +
                "WHEN temp2.`status`='1' THEN '已完成' " +
                "WHEN temp2.`status`='2' THEN '关闭' " +
                "ELSE '' END statusName ");
        sqlStr.append("FROM t_crm_task AS temp2 " +
                "LEFT JOIN t_bc_param_def paramDef ON temp2.type_code = paramDef.`code` ");
        List<Object> paramList = new ArrayList<>();
        this.createTaskSql(typeCode, typePCode, title, plateNum, companyId, sqlStr, paramList, "temp2");
        sqlStr.append(" order by temp2.expect_date ASC ");
        System.out.println(sqlStr);
        return this.taskDao.findTransferTaskList(start, limit, sqlStr.toString(), paramList);
    }

    /**
     * 迁移选定的任务信息
     *
     * @param data
     */
    @Override
//    @Transactional
    public void transferTask(List<Map> data, String companyId) throws Exception {
        if (data != null) {
            if (data.size() > 0) {
                StringBuffer sqlStr = new StringBuffer();
                List<Object> paramList = new ArrayList<>();
                //遍历任务 生成相应语句 只需要ID信息
                this.createTaskSql(sqlStr, data, paramList);
                StringBuffer sqltemp1 = new StringBuffer();
                sqltemp1.append("select id,   create_time,   create_user,   update_time,   update_user,   vin,   car_id,   company_id,   contact_id,   content,   deal_date,   expect_date,   is_back,   link_man,   link_phone,   plateNum,   sa_id,   STATUS,   type_code,   type_pcode,   assign_id,   assign_name,   dept_id,store_name,store_area,store_p_area,store_chanel,store_area_code,store_p_area_code,store_code  from t_crm_task temp2 ");
                sqltemp1.append(sqlStr);
                sqltemp1.append("and not EXISTS (  select 1 from t_crm_task_real where car_id=temp2.car_id and plateNum=temp2.plateNum and company_id=temp2.company_id )");
                //将选定的任务插入到指定库表中
                insertRealTask(sqltemp1.toString(), paramList);
                //将选定的任务从临时表中删除
                deleteTempTask(sqlStr.toString(), paramList);
//                timeTaskService.updCrmTaskSaId(companyId);
                taskConfService.distTaskByTaskConf(companyId);
            }
        }
    }

    /**
     * 根据条件生成ID语句
     *
     * @param sqlStr
     * @param data
     * @param paramList
     */
    private void createTaskSql(StringBuffer sqlStr, List<Map> data, List<Object> paramList) {
        sqlStr.append("  where temp2.id in (");
        for (int i = 0; i < data.size(); i++) {
            Map map = data.get(i);
            String id = String.valueOf(map.get("id"));
            sqlStr.append("?");
            if (i < (data.size() - 1)) {
                sqlStr.append(",");
            }
            paramList.add(id);
        }
        sqlStr.append(")");
    }

    /**
     * 根据条件查询任务 进行迁移
     *
     * @param typeCode
     * @param typePCode
     * @param title
     * @param plateNum
     * @param companyId
     * @throws Exception
     */
    @Override
//    @Transactional
    public void transferTask(String typeCode, String typePCode, String title, String plateNum, String companyId) throws Exception {
        //必须输入公司ID
        if (StringUtils.isEmpty(companyId)) {
            return;
        }
        //事务处理
        StringBuffer sqlStr = new StringBuffer();
        List<Object> paramList = new ArrayList<>();
        //排除
        sqlStr.append("select temp2.id,temp2.create_time,temp2.create_user,temp2.update_time,temp2.update_user,temp2.vin," +
                "temp2.car_id,temp2.company_id,temp2.contact_id,temp2.content,temp2.deal_date,temp2.expect_date,temp2.is_back," +
                "temp2.link_man,temp2.link_phone,temp2.plateNum,temp2.sa_id,temp2.status,temp2.type_code,temp2.type_pcode," +
                "temp2.assign_id,temp2.assign_name,temp2.dept_id,temp2.store_name,temp2.store_area,temp2.store_p_area,temp2.store_chanel,temp2.store_area_code,temp2.store_p_area_code,temp2.store_code from t_crm_task temp2 ");
        StringBuffer sqlTemp2 = new StringBuffer();
        List<Object> paramListTemp2 = new ArrayList<>();
        this.createTaskSql(typeCode, typePCode, title, plateNum, companyId, sqlTemp2, paramListTemp2, "temp2");
        sqlStr.append(sqlTemp2.toString());
        paramList.addAll(paramListTemp2);
        //排除正式表中的数据
        sqlStr.append(" and  NOT EXISTS( ");
        sqlStr.append("select 1 from t_crm_task_real temp1 ");
        this.createTaskSql(typeCode, typePCode, title, plateNum, companyId, sqlStr, paramList, "temp1");
        sqlStr.append(" AND temp1.plateNum=temp2.plateNum AND temp1.expect_date=temp2.expect_date AND temp1.type_code=temp2.type_code AND temp1.type_code<>'zdy')");

        //先将数据迁移至正式表
        insertRealTask(sqlStr.toString(), paramList);
        //在将迁移数据删除
        deleteTempTask(sqlTemp2.toString(), paramListTemp2);
        //失败 回滚 成功 提示
//        timeTaskService.updCrmTaskSaId(companyId);
        taskConfService.distTaskByTaskConf(companyId);
    }

    /**
     * 根据条件生成查询语句
     *
     * @param typeCode
     * @param typePcode
     * @param title
     * @param plateNum
     * @param companyId
     * @param sqlStr
     * @param paramList
     */
    private void createTaskSql(String typeCode, String typePcode, String title, String plateNum, String companyId,
                               StringBuffer sqlStr, List<Object> paramList, String byname) {
        sqlStr.append(" WHERE 1=1 ");

        if (!StringUtils.isEmpty(typePcode)) {
            sqlStr.append(" AND " + byname + ".type_pcode like ? ");
            paramList.add(typePcode + "%");

            if (!StringUtils.isEmpty(typeCode)) {
                if("bx".equals(typePcode)){
                    sqlStr.append(" AND " + byname + ".type_code like ? ");
                    paramList.add(typeCode+"%");
                }else{
                    sqlStr.append(" AND " + byname + ".type_code=? ");
                    paramList.add(typeCode);
                }

            }
        }





        if (!StringUtils.isEmpty(title)) {
            sqlStr.append(" AND " + byname + ".content like ? ");
            paramList.add(title);
        }
        if (!StringUtils.isEmpty(plateNum)) {
            sqlStr.append(" AND " + byname + ".content like ? AND " + byname + ".plateNum like ? ");
            paramList.add("%" + plateNum + "%");
            paramList.add("%" + plateNum + "%");
        }
        if (!StringUtils.isEmpty(companyId)) {
            sqlStr.append(" AND " + byname + ".company_id =? ");
            paramList.add(companyId);
        }
    }

    /**
     * 将任务信息插入指定表中
     *
     * @param sql
     * @param paramList
     * @throws Exception
     */
    private void insertRealTask(String sql, List<Object> paramList) throws Exception {
        if (paramList.size() == 0) {
            return;
        }
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("insert into t_crm_task_real(id, create_time, create_user, update_time, update_user, vin, car_id, company_id," +
                " contact_id, content, deal_date, expect_date, is_back, link_man, link_phone, plateNum, sa_id, status, type_code, " +
                "type_pcode, assign_id, assign_name, dept_id,store_name,store_area,store_p_area,store_chanel,store_area_code,store_p_area_code,store_code ) (  ");
        sqlStr.append(sql);
        sqlStr.append(" )");
        System.out.println(sqlStr.toString());
        int nums = taskDao.executeSQL(sqlStr.toString(), paramList.toArray());
        System.out.println(nums + "执行条数");
        //insert into t_crm_task_real  select * from t_crm_task
    }

    /**
     * 删除原有数据
     *
     * @param sql
     * @param paramList
     * @throws Exception
     */
    private void deleteTempTask(String sql, List<Object> paramList) throws Exception {
        if (paramList.size() == 0) {
            return;
        }
        //delete from (select * from t_crm_car_task)
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("DELETE temp2 FROM t_crm_task temp2 ");
        sqlStr.append(sql);
        taskDao.executeSQL(sqlStr.toString(), paramList.toArray());
    }

    /**
     * 清理任务
     *
     * @param companyId
     * @throws Exception
     */
    @Override
    public void cleanTask(String companyId) throws Exception {
        taskDao.cleanTask(companyId);
    }

    /**
     * 生成任务
     *
     * @param companyId
     * @throws Exception
     */
    @Override
    public void createTask(String companyId, String startTime, String endTime) throws Exception {
        //生成关爱类任务
        createGuanAiTask(companyId, startTime, endTime);
        //生成保险类任务
        createBaoXianTask(companyId, startTime, endTime);
        //生成年审类任务
        this.nianShen(companyId, startTime, endTime);
        //生成保养类任务
        createBaoYangTask(companyId, startTime, endTime);
        //生成流失类任务
        this.liuShi(companyId, startTime, endTime);
    }

    public void createBaoYangTask(String companyId, String startTime, String endTime) throws Exception {
        crmCarMaintainTaskService.carMaintain(companyId, startTime, endTime);
        twoCrmCarMaintainTaskService.carMaintain(companyId, startTime, endTime);
        dbCarMaintainTaskService.carMaintain(companyId, startTime, endTime);
        firstMonthMaintainTaskService.carMaintain(companyId, startTime, endTime);

    }

    private void createGuanAiTask(String companyId, String startTime, String endTime) throws Exception {
        //生日
        this.guanAiShengRi(companyId, startTime, endTime);
        //质保
        this.guanAiZhiBao(companyId, startTime, endTime);
        //延保
        this.guanAiYanBao(companyId, startTime, endTime);
        //卡券
        crmTimeTaskService.couponExpireRemindByCompnayQuartz(companyId, startTime, endTime);

    }

    private void createBaoXianTask(String companyId, String startTime, String endTime) throws Exception {
        this.xuBaoShangYe(companyId, startTime, endTime);
        this.xuBaoJiaoQiang(companyId, startTime, endTime);
        this.jieDaiShangYe(companyId, startTime, endTime);
        this.jieDaiJiaoQiang(companyId, startTime, endTime);
        this.baoXianHuoYue(companyId, startTime, endTime);
        this.baoXianFeiHuoYue(companyId, startTime, endTime);
        this.ciNianJiaoQiang(companyId, startTime, endTime);
        this.ciNianShangYe(companyId, startTime, endTime);
    }
    //-----------------------------------------车辆任务预生成结束--------------------------------

    private enum CARREFTYPE {
        ALL, LASTNUMBER, MATISSA
    }

    /**
     * 任务处理下拉：所有可处理任务人的及待处理的任务数
     *
     * @author 张国凯
     * @desc 修正公司查询方式，与将任务数改为未完成的任务数
     */
    @Override
    public List<Map> combo(String uid, String companyId, String area, String brand) throws Exception {
        if ("".equals(companyId)) {
            companyId = TokenUtil.getCompanyId(uid);
        }
        boolean isManger = TokenUtil.isCompanyAdmin(uid);
        StringBuffer sql = new StringBuffer();
        List params = new ArrayList();
//        List<String> deptList = new ArrayList<>();
//        deptList.add(companyId);
//        crmCarRefService.buildDeptChild(deptList, companyId);
        sql.append("select u.u_name,t.taskNums,u.id from t_bc_user u, " +
                " (select COUNT(id) taskNums,sa_id from");
        sql.append(" ( select id,sa_id from t_crm_task_real ");
//        sql.append(" (select * from t_bc_organ  where 1=1  ");
//        //Constant.companyAndAreaAndBrand(uid, companyId, area, brand, sql, params, "");
//        sql.append(" ) bc ");
        sql.append(" where status='0' and company_id= ?   ");
        params.add(companyId);
        if (!isManger) {//不是管理员时 查询自己
            sql.append(" AND sa_id=? ");
            params.add(uid);
        }

        sql.append(" ) task group by task.sa_id  ");
        sql.append(") t where u.id=t.sa_id ");

        // 门店人员过滤（管理员只能看本门店下的人员）
        Map deptMap = TokenUtil.getDept(uid);
        String oPid = (String) deptMap.get("oPId");
        Map organInfoMap = TokenUtil.getOrgInfo(uid);
        if(oPid.equals("8a9aa28460026f310160059c8dd804b2")){ // 如果是门店管理员
            sql.append(" and u.o_id = ? ");
            params.add(organInfoMap.get("deptId"));
        }


        List<Map<String, Object>> mapList = this.taskDao.list(sql.toString(), params.toArray());
        List<Map> resList = new ArrayList<Map>();
        Map temp;
        for (Map map : mapList) {
            temp = new HashMap();
            temp.put("value", map.get("id"));
            temp.put("label", map.get("uName"));
            temp.put("sum", map.get("taskNums"));
            resList.add(temp);

        }
        return resList;
    }

    /**
     * 根据人员车牌号规则，每个人所处理的任务数
     *
     * @throws Exception
     */
    @Override
    public void countSaIDNums() throws Exception {
        //1、获得分组公司ID
        List<Map<String, Object>> companyList = crmCarRefDao.findCompanyList();
        for (Map<String, Object> company : companyList) {
            String companyId = String.valueOf(company.get("company_id"));
            //2、获得公司下的人员
            List<Map<String, Object>> carRefUserList = crmCarRefDao.findUserByCompnayIdList(companyId);
            //3、遍历人员信息
            for (Map<String, Object> carRefUser : carRefUserList) {
                String uid = String.valueOf(carRefUser.get("u_id"));
                Map<String, Object> userMap = taskDao.findUserById(uid);
                //4、获得人员车辆分配规则
                List<Map<String, Object>> datas = this.carRefDao.findCarRefByUid(companyId, uid);
                if (datas == null) {
                    continue;
                }
                if (datas.size() <= 0) {
                    continue;
                }
                // 判断人员的类型 如果为0 则不处理 1 2则去处理
                String carRefType = "0";
                if (carRefType.equals(CARREFTYPE.ALL.toString())) {
                    continue;
                }
                String sql = this.carRefSql(datas, uid);
                if (sql != null) {
                    Integer countTask = taskDao.findTaskCount(sql, uid);
                    TCrmTaskUserCount userCount = userCountDao.findUserCountByUserIdAndCompanyId(uid, companyId);
                    if (userCount == null) {
                        userCount = new TCrmTaskUserCount();
                        userCount.setCounts(countTask);
                        userCount.setuId(uid);
                        userCount.setCompanyId(companyId);
                        userCount.setuName(String.valueOf(userMap.get("u_name")));
                    } else {
                        userCount.setCounts(countTask);
                    }
                    //保存用户未处理任务数量
                    userCountDao.saveOrUpdate(userCount);
                    //更新用户SAID
                }
            }
        }
    }

    private String carRefSql(List<Map<String, Object>> datas, String uid) {
        StringBuffer sqlStr = new StringBuffer();
        sqlStr.append("select count(id) count from ( " +
                "select * from t_crm_task_real where 1=1 ");
        Map<String, Object> has = new HashMap<String, Object>();
        String tmp = null;
        String tmps[] = null;
        //可能存在单个value字段1，2，3样式，需先分割开
        for (int i = 0; i < datas.size(); i++) {
            if (datas.get(i).get("value") == null) {
                continue;
            }
            tmp = datas.get(i).get("value").toString();
            tmps = tmp.split(",");
            for (int j = 0; j < tmps.length; j++) {
                has.put(tmps[j], "1");
            }
        }
        Object obj[] = has.keySet().toArray();
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < obj.length; i++) {
            sb.append(obj[i].toString());
        }
        //0全部、1 尾号、2 尾数、3 VIN、
        //生成过滤语句
        String type = datas.get(0).get("type").toString();
        if (type.equals(String.valueOf(CARREFTYPE.LASTNUMBER.ordinal()))) {
            sqlStr.append(" AND  plateNum REGEXP '[" + sb.toString().replaceAll(",", "") + "]$' ");
        } else if (type.equals(String.valueOf(CARREFTYPE.MATISSA.ordinal()))) {
            sqlStr.append(" AND plateNum REGEXP '.*[" + sb.toString().replaceAll(",", "") + "][A-Za-z]*$' ");
        } else {//全部
            //分配全部车辆
            return null;
        }
        sqlStr.append("and sa_id is NULL and `status`=0 ");
        sqlStr.append("UNION " +
                "select * from t_crm_task_real where sa_id=? and `status`=0) t");
        return sqlStr.toString();
    }

    /**
     * 统计指定公司的任务各类型下的任务数量
     *
     * @param companyId
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> taskCountByType(String companyId) throws Exception {
        List<Map<String, Object>> list = new ArrayList<>();
        List<Map<String, Object>> pCodeList = taskDao.taskCountByPcode(companyId);
        if (pCodeList != null) {
            Map<String, String> typeName = setTypeCodeName();
//            List<Map<String,Object>> codeList=taskDao.taskCountByCode(companyId);
            for (Map<String, Object> pcodeMap : pCodeList) {
                String pcode = String.valueOf(pcodeMap.get("type_pcode"));
                String pname = typeName.get(pcode.toLowerCase());
                pcodeMap.put("name", pname);
                list.add(pcodeMap);
//                for (Map<String,Object> codeMap:codeList) {
//                    String typePcode=String.valueOf(codeMap.get("type_pcode"));
//                    if(pcode.equals(typePcode)){
//                        String typecode=String.valueOf(codeMap.get("type_code"));
//                        codeMap.put("name",typeName.get(typecode.toLowerCase()));
//                        list.add(codeMap);
//                    }
//                }
            }
        }
        return list;
    }

    private Map<String, String> setTypeCodeName() {
        Map<String, String> map = new HashMap<>();
        map.put("bx", "保险");
        map.put("bxcn", "保险");
        map.put("bxxb", "保险");
        map.put("bxhy", "保险");
        map.put("bxfhy", "保险");
        map.put("bxjd", "保险");
        map.put("cnj", "次年交强险");
        map.put("cns", "次年商业险");
        map.put("xbj", "交强险续保");
        map.put("xbs", "商业险续保");
        map.put("hyt", "保险活跃天数");
        map.put("fhyt", "保险非活跃天数");
        map.put("jdj", "交强险提醒天数");
        map.put("jds", "商业险接待天数");
        map.put("ga", "关爱");
        map.put("sr", "生日");
        map.put("ns", "年审");
        map.put("zbt", "质保");
        map.put("ybt", "延保");
        map.put("ls", "流失");
        map.put("6", "六月");
        map.put("7", "七月");
        map.put("8", "八月");
        map.put("9", "九月");
        map.put("10", "十月");
        map.put("11", "十一月");
        map.put("12", "十二月");
        map.put("by", "保养");
        map.put("sb_mbr", "首保");
        map.put("sb_mby", "首保月");
        map.put("eb_mbr", "二保");
        map.put("eb_mby", "二保月");
        map.put("db_mbr", "定保");
        map.put("db_mby", "定保月");
        map.put("sy_mbr", "新车首月");
        map.put("zdy", "自定义");
        return map;
    }

    /**
     * 检测操作人 与任务车牌号之间的处理权限
     *
     * @param uid
     * @param companyId
     * @param plateNum
     * @return
     * @throws Exception
     */
    @Override
    public boolean checkTaskPower(String uid, String companyId, String plateNum) throws Exception {
//        List<Map<String, Object>> datas = this.carRefDao.findCarRefByUid(companyId, uid);
//        StringBuffer sqlStr=new StringBuffer();
//        sqlStr.append("select * from t_crm_task_real crmtask where plateNum=? ");
//        List<String> ps=new ArrayList<>();
//        ps.add(plateNum);
//        this.carRefSql(companyId,uid,sqlStr,ps,datas,TASKPOWER.HANDLE.ordinal());
//        List<Map<String,Object>> mapList=this.taskDao.list(sqlStr.toString(),ps.toArray());
//        if(mapList!=null){
//            if(mapList.size()>0){
//                return true;
//            }
//        }
        return false;
    }

    /**
     * 任务二次指派
     *
     * @return
     * @throws Exception
     */
    @Override
    public Integer distTask(String pcode, String code, String uid, String title, int type, String allotstatus,
                            String allotType, String allotTypeV, Map data1, String saId, boolean force,
                            String deal, String taskType, String series, String acTag) throws Exception {
        List<Map> data = (List<Map>) data1.get("arr");
       // List nameList = (List) data1.get("belongsStoreName"); //
        List<Map> belongsStoreNames = (List<Map>) data1.get("belongsStoreName");
        if (StringUtils.isEmpty(saId)) {
            throw new Exception("请选择要分配的人员");
        }
        if(force){
            if("".equals(pcode)){
                throw new Exception("请先选择任务分类!!");
            }
            if("".equals(code)){
                throw new Exception("请选择任务分类及二级分类!!");
            }
        }
        //***
        // 多个 SA
        // **/
        if (saId.indexOf(",") > 0) {
            // 单个 SA
            if (data != null && data.size() > 0) {
                // 如果已选择任务，则只支持分配给单人
                throw new Exception("选中任务分配时，请选择单个处理人员！");
            }
            return distTaskToMore(pcode, code, uid, title, type, allotstatus,
                    allotType, allotTypeV, data, saId, force,
                    deal, taskType, series, acTag,belongsStoreNames);
        } else {
            // 单个 SA

            return distTaskToOne(pcode, code, uid, title, type, allotstatus,
                    allotType, allotTypeV, data, saId, force,
                    deal, taskType, series, acTag,belongsStoreNames);
        }
    }

    /*****
     * 分配任务一个人
     * @return
     * @throws Exception
     */
    public Integer distTaskToOne(String pcode, String code, String uid, String title, int type, String allotstatus,
                                 String allotType, String allotTypeV, List<Map> data, String saId, boolean force,
                                 String deal, String taskType, String series, String acTag,List belongsStoreNames) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        StringBuffer sqlStr = new StringBuffer();
        List<String> ps = new ArrayList<>();
//        sqlStr.append("select * from ");

        sqlStr.append(" update t_crm_task_real crmtask set crmtask.sa_id =? ");
        ps.add(saId);
        sqlStr.append(" WHERE  crmtask.company_id = ? ");
        ps.add(companyId);

        Map organInfo = this.getOrganInfo(uid);
        if(organInfo != null){
            if(organInfo.get("oPId").equals("8a9aa28460026f310160059c8dd804b2")){ // 如果是所属连锁店的门店，则将本店字段默认为本店
                //sqlStr.append("AND crmtask.store_name = ? ");
                //ps.add(organInfo.get("oName").toString());
                belongsStoreNames.add(0,organInfo.get("oName"));
            }
        }

        if(belongsStoreNames.size()>0){
            String s = (String) belongsStoreNames.get(0);
            sqlStr.append(" AND crmtask.store_name = ? ");
            ps.add(s);
        }

        if (data != null && data.size() > 0) {
            sqlStr.append(" and id in(");
            for (int i = 0; i < data.size(); i++) {
                Map<String, Object> map = data.get(i);
                String id = String.valueOf(map.get("id"));
                ps.add(id);
                sqlStr.append("?");
                if (i < (data.size() - 1)) {
                    sqlStr.append(",");
                }
            }
            sqlStr.append(")");
        } else {
            if (!StringUtils.isEmpty(title)) {
                sqlStr.append("and ( crmtask.content like ? or crmtask.plateNum like ? ) ");
                ps.add("%" + title + "%");
                ps.add("%" + title + "%");
            }
            //类型大类
            if (!StringUtils.isEmpty(pcode)) {
                sqlStr.append(" AND crmtask.type_pcode = ?  ");
                ps.add(pcode);
            }
            //类型小类
            if (!StringUtils.isEmpty(code)) {
                if ("bx".equals(pcode)) {
                    sqlStr.append(" AND crmtask.type_code like  ?");
                    ps.add(code + "%");
                } else {
                    sqlStr.append(" AND crmtask.type_code = ?");
                    ps.add(code);
                }
//                sqlStr.append(" AND crmtask.type_code = ?");
//                ps.add(code);
            }
            //根据 UID 查询车辆分配规则
            //不是管理员是需要增加分配规则条件
            //管理员角色可以不遵守
            boolean isManger = TokenUtil.isCompanyAdmin(uid);
            if (!isManger) {
                this.roleAndCar(uid, companyId, type, sqlStr, ps);
            }
            if (!"".equals(allotTypeV)) {
                String str[] = allotTypeV.split(",");
                StringBuffer sb = new StringBuffer();
                for (int i = 0; i < str.length; i++) {
                    sb.append(str[i].toString());
                    sb.append(",");
                }
                sb.deleteCharAt(sb.length() - 1);
                //0全部、1 尾号、2 尾数、3 VIN、
                // StringBuffer pdsql = new StringBuffer();
                //生成过滤语句
                if (allotType.equals("1")) {
                    sqlStr.append(" AND  crmtask.plateNum REGEXP '[" + sb.toString().toUpperCase().replaceAll(",", "") + "]$'");
                } else if (allotType.equals("2")) {
                    sqlStr.append(" AND crmtask.plateNum REGEXP '.*[" + sb.toString().toUpperCase().replaceAll(",", "") + "][A-Za-z]*$'");
                } else if (allotType.equals("3")) {
                    sqlStr.append(" AND EXISTS (select * from t_crm_task_car_vin_detail where crmtask.vin=vin and u_id=? and car_vin_id=? and company_id=?)");
                    ps.add(uid);
                    ps.add(allotTypeV);
                    ps.add(companyId);
                }
            }
        }
        //force true 强制分配 则不去关心sa_id是否已经分配
        //force false 不强制分配 只分配未分配的任务
        if (!force) {
            sqlStr.append(" and crmtask.sa_id is null ");
        }
        if (!"".equals(deal)) {
            sqlStr.append(" and crmtask.sa_id=? ");
            ps.add(deal);
        }
        // 任务分配时，仅分配未完成的任务，已完成或已关闭不再处理
        //if (!"".equals(taskType)) {
        sqlStr.append(" AND crmtask.status='0' ");
        // ps.add(taskType);
        // }
        // 增加车辆属性
        StringBuffer temp = new StringBuffer();
        List tempParam = new ArrayList();
        if (!StringUtils.isEmpty(series)) {
            temp.append("AND  locate(? ,c.series) ");
            tempParam.add(series);
        }

        if (!StringUtils.isEmpty(acTag)) {
            temp.append(" AND  c.active = ? ");
            tempParam.add(acTag);
        }
        if (temp.length() > 5) {
            temp.toString().replaceFirst("AND", "");
            sqlStr.append(" AND EXISTS ( select 1 from t_crm_car c where c.company_id=crmtask.company_id and c.vin = crmtask.vin ");
            sqlStr.append(temp.toString());
            sqlStr.append(" ) ");
            ps.addAll(tempParam);
        }

        //TODO:测试打印sql语句和参数信息
        System.out.println(sqlStr.toString());
        for (int i = 0; i < ps.toArray().length; i++) {
            System.out.println(ps.toArray()[i]);
        }
        int nums = this.taskDao.executeSQL(sqlStr.toString(), ps.toArray());
        return nums;
    }

    /******
     * 分配任务给多个人
     */
    public Integer distTaskToMore(String pcode, String code, String uid, String title, int type, String allotstatus,
                                  String allotType, String allotTypeV, List<Map> data, String saId, boolean force,
                                  String deal, String taskType, String series, String acTag,List belongsStoreNames) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        StringBuffer sqlStr = new StringBuffer();
        List<String> ps = new ArrayList<>();
//        sqlStr.append("select * from ");
        /****
         * 1. 对任务按照 VIN 分组，取最小的提醒日期
         * 2. 按条件查询出 VIN，VIN 按照提醒日期升序排序
         * 3. 把 VIN 平均分配给指定的 SA
         * 4. 遍历SA，进行任务指派工作
         * **/

        // 任务查询语句与参数拼接
        sqlStr.append(" WHERE  crmtask.company_id = ? ");
        ps.add(companyId);

        Map organInfo = this.getOrganInfo(uid);
        if(organInfo != null){
            if(organInfo.get("oPId").equals("8a9aa28460026f310160059c8dd804b2")){ // 如果是所属连锁店的门店，则将本店字段默认为本店
                //sqlStr.append("AND crmtask.store_name = ? ");
                //ps.add(organInfo.get("oName").toString());
                belongsStoreNames.add(0,organInfo.get("oName"));
            }
        }

        if(belongsStoreNames.size()>0){
            String s = (String) belongsStoreNames.get(0);
            sqlStr.append(" AND crmtask.store_name = ? ");
            ps.add(s);
        }

        if (!StringUtils.isEmpty(title)) {
            sqlStr.append(" and ( crmtask.content like ? or crmtask.plateNum like ? ) ");
            ps.add("%" + title + "%");
            ps.add("%" + title + "%");
        }
        //类型大类
        if (!StringUtils.isEmpty(pcode)) {
            sqlStr.append(" AND crmtask.type_pcode = ?  ");
            ps.add(pcode);
        }
        //类型小类
        if (!StringUtils.isEmpty(code)) {
            if ("bx".equals(pcode)) {
                sqlStr.append(" AND crmtask.type_code like  ?");
                ps.add(code + "%");
            } else {
                sqlStr.append(" AND crmtask.type_code = ?");
                ps.add(code);
            }
//                sqlStr.append(" AND crmtask.type_code = ?");
//                ps.add(code);
        }

        //force true 强制分配 则不去关心sa_id是否已经分配
        //force false 不强制分配 只分配未分配的任务
        if (!force) {
            sqlStr.append(" and (crmtask.sa_id is null or crmtask.sa_id = '' )");
        }
        if (!"".equals(deal)) {
            sqlStr.append(" and crmtask.sa_id=? ");
            ps.add(deal);
        }
        // 仅分配未完成任务
//        if (!"".equals(taskType)) {
//            sqlStr.append(" AND crmtask.status=? ");
//            ps.add(taskType);
//        }
        sqlStr.append(" AND crmtask.status='0' ");

        // 增加车辆属性
        StringBuffer tempBuf = new StringBuffer();
        List tempParam = new ArrayList();
        if (!StringUtils.isEmpty(series)) {
            tempBuf.append("AND  locate(? ,c.series) ");
            tempParam.add(series);
        }

        if (!StringUtils.isEmpty(acTag)) {
            tempBuf.append(" AND  c.active = ? ");
            tempParam.add(acTag);
        }
        if (tempBuf.length() > 5) {
            tempBuf.toString().replaceFirst("AND", "");
            sqlStr.append(" AND EXISTS ( select 1 from t_crm_car c where c.company_id=crmtask.company_id and c.vin = crmtask.vin ");
            sqlStr.append(tempBuf.toString());
            sqlStr.append(" ) ");
            ps.addAll(tempParam);
        }
        // 查询对应任务

        // 增加车辆等级 和 车系


        // 查询待分配车辆
        StringBuffer findBuffer = new StringBuffer();
        findBuffer.append(" select temp.vin from ( ");
        findBuffer.append(" select crmtask.vin,min(crmtask.expect_date) as expect_date from t_crm_task_real crmtask  ");
        findBuffer.append(sqlStr.toString());
        findBuffer.append(" group by crmtask.vin ");
        findBuffer.append(" ) temp  order by expect_date asc ");

        List<Map<String, Object>> taskList = this.taskDao.list(findBuffer.toString(), ps.toArray());
        //分配车辆给不同的 SA
        //检验 SA
        /*****
         * 判断SA的数量，去除为空的数据
         */
        String[] hands = saId.split(",");
        List<String> handList = new ArrayList<String>();
        for (String temp : hands) {
            if (!StringUtils.isEmpty(temp)) {
                handList.add(temp);
            }
        }
        //String[] saUsers = (String[]) handList.toArray();
        int userCount = handList.size();
        // 遍历分配
        //当前用户 ID
        String curUid = "";
        //当前车辆 VIn
        String curVin = "";
        //临时字符串
        String tempStr = "";
        Map<String, String> vinUser = new HashMap<String, String>();
        for (int i = 0; i < taskList.size(); i++) {
            curUid = handList.get(i % userCount);
            curVin = String.valueOf(taskList.get(i).get("vin"));
            if (vinUser.containsKey(curUid)) {
                tempStr = vinUser.get(curUid);
                vinUser.put(curUid, tempStr + ",'" + curVin + "'");
            } else {
                vinUser.put(curUid, "'" + curVin + "'");
            }
        }
        // 获取对应人 与 车辆的 VIN
        // 生成 分配的 SQL
        // 查询待分配车辆

        StringBuffer distBuffer = new StringBuffer();

        distBuffer.append(" update  t_crm_task_real crmtask set sa_id = ? ");
        distBuffer.append(sqlStr.toString());
        // ps 查询条件参数
        // distBuffer.append(" AND crmtask.vin in (? ) ");
        int nums = 0;
        //批量分配任务
        List distParam;
        String distSql = "";
        for (String tempUid : handList) {
            //对每个人员调整
            distParam = new ArrayList();
            distParam.add(tempUid);
            distParam.addAll(ps);
            distSql = distBuffer.toString() + " AND crmtask.vin in (" + vinUser.get(tempUid) + " )";
            //distParam.add(vinUser.get(tempUid));
            nums += this.taskDao.executeSQL(distSql, distParam.toArray());
        }

        return nums;
    }

    /********
     * 生成Execl
     * @param dataList 导出数据
     * @param uid 导出用户
     * @return
     */
    @Override
    public XSSFWorkbook createExcel(List<Map<String, Object>> dataList, String uid) {
        //导出的任务Excel的标题
        String[] excelHeaderRecordout = {"任务类别", "提醒日期", "完成日期", "车架号", "车牌号", "联系人", "联系电话", "任务标题", "任务状态", "处理人", "SA服务顾问", "是否回厂"};

        XSSFWorkbook wb = new XSSFWorkbook();

        XSSFSheet sheet = wb.createSheet("任务详情");
        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(style);
            sheet.autoSizeColumn(i);
            sheet.setDefaultRowHeightInPoints(24);
            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }
        /*****
         * int的最大值是2147483647，不会越界
         * 理论上可导出的数据量不会超过65535,出现这种情况之后，再进行优化处理
         * **/
        if (dataList != null && dataList.size() > 0) {
            int dataSize = dataList.size();
            String temp;
            for (int i = 0; i < dataSize; i++) {
                Map map = (Map) dataList.get(i);
                row = sheet.createRow(i + 1);
                temp = String.valueOf(map.get("typeName"));
                row.createCell(0).setCellValue(temp.equals("null") ? "" : temp);
                temp = String.valueOf(map.get("expectDate"));
                row.createCell(1).setCellValue(temp.equals("null") ? "" : temp);
                temp = String.valueOf(map.get("dealDate"));
                row.createCell(2).setCellValue(temp.equals("null") ? "" : temp);
                temp = String.valueOf(map.get("vin"));
                row.createCell(3).setCellValue(temp.equals("null") ? "" : temp);
                temp = String.valueOf(map.get("plateNum"));
                row.createCell(4).setCellValue(temp.equals("null") ? "" : temp);
                temp = String.valueOf(map.get("ownerName"));
                row.createCell(5).setCellValue(temp.equals("null") ? "" : temp);
                temp = String.valueOf(map.get("ownerTel"));
                row.createCell(6).setCellValue(temp.equals("null") ? "" : temp);
                temp = String.valueOf(map.get("content"));
                row.createCell(7).setCellValue(temp.equals("null") ? "" : temp);

                temp = String.valueOf(map.get("statusName"));
                row.createCell(8).setCellValue(temp.equals("null") ? "" : temp);

                temp = String.valueOf(map.get("uname"));
                row.createCell(9).setCellValue(temp.equals("null") ? "" : temp);

                temp = String.valueOf(map.get("saName"));
                row.createCell(10).setCellValue(temp.equals("null") ? "" : temp);
                temp = String.valueOf(map.get("isBack"));
                row.createCell(11).setCellValue(temp.equals("1") ? "是" : "否");

            }
        }

        return wb;

    }

    /**
     * @Name: 获取当天保险任务的车牌号
     * @Author: Liu XiaoLong
     * @Date: 2017/12/21 11:36
     * @Remark:
     */
    @Override
    public Map getPlateNumForInsuranceTaskToday(String uid) throws Exception {
        String companyId = TokenUtil.getCompanyId(uid);
        Date date = new Date();
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("YYYY-MM-dd");
        String strDate = simpleDateFormat.format(date);
        List list = itCrmTaskRealMapper.getPlateNumForInsuranceTaskToday(strDate,companyId);
        Map map = new HashMap();
        map.put("dataList",list);
        return map;
    }

    private TCrmTaskAssignLog getTaskAssignLog(Map<String, Object> map, String uid, String uname, String taskId, Map<String, Object> user) {
        if (map.get("saId") != null) {
            String mSaId = String.valueOf(map.get("saId"));

            if (!"".equals(mSaId)) {
                //原处理人
                String assignId = String.valueOf(map.get("assignId"));
                String assignName = String.valueOf(map.get("assignName"));
                String saId = String.valueOf(map.get("saId"));
                String usaName = "";
                if (map.get("uname") != null) {
                    usaName = String.valueOf(map.get("uname"));
                }
                TCrmTaskAssignLog log = new TCrmTaskAssignLog();
                log.setOriginalUid(assignId);//原处理人ID
                log.setOriginalUname(assignName);//原处理人
                log.setNewUid(uid);//新处理人ID
                log.setNewUname(uname);//新处理人
                log.setCreateTime(new Date().getTime());
                log.setCreateUser(uid);
                log.setTaskId(taskId);//任务ID
                log.setOriginalHandleId(saId);//原处理人ID
                log.setOriginalHanleName(usaName);//原处理人Name
                log.setNewlHandleId(String.valueOf(user.get("id")));
                log.setNewlHanleName(String.valueOf(user.get("uName")));
                return log;
            }
        }
        return null;
    }

    public static void main(String[] args) {
        System.out.println("aBC".toLowerCase());
    }

}
